2

I have a column A with checkboxes and a column G with Currency

can I do a formula to get the total of G only when the checkbox is not tick?

in the same idea, I have some dates in column B, and I like to get also the total of G (doesn't mater the checkbox this time) when in the current month (or the idea between a 30days period, I suppose something to do with DATEIF) but this I suppose I can do with something like :

SUMIF(B,MONTH(6),G) however this is not working

2

A look at SUMIFS and SUMIF in the help files will point you in the right direction.

First case:

Total all currency in column when checkbox is not ticked. A checkbox not ticked returns false, whereas ticked is true. The column headers are named in the example table so the formula in the screenshot reflects those names in the place of the column letter designations.

Formula: =SUMIF(A, false, G)

Formula

Second case:

Total by month. The trick is to get a valid date value. The formula below is a starting point. Formula for June in the example below: =SUMIFS($Currency,$Date,">=2018/"&MONTH(D7)&"/01",$Date,"<=2018/"&MONTH(D7)&"/30")

For details on the SUMIFS function, see the help documents.

SUMIFS formula

NOTE: the Month 'June' is actually a date 2018/6/1 formatted as month name in this example. One could substitute the current month this way: MONTH(NOW) but note that the formula will change each time the file is opened. So if opened in July, that will be reflected in the formula. See the This month total in the example below.

Formula example

Sample Table

Example Table

  • that's perfect thank you, I didn't think checkbox was actually simple boolean but that actually make sense – Kevin Jun 29 '18 at 22:58
  • Glad it worked! – bjbk Jun 30 '18 at 2:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .