iworknumbers
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
Using the SUM(IF) function:
Because Numbers does not yet support certain Array functions, for example extracting MONTH(A1:A23), by parsing the date into a third column (which can be hidden) the function will work. This column can be in any table, not just the raw data table as long as the references are valid.
Hidden column formula:
=MONTH(A1)&YEAR(A1)
Where A1 is the cell with date.
Copy down then hide column if desired. (Importing more data by adding rows should automatically bring the formula down but unhide the column to be sure.)
=SUMIF($'Month & Year',"="&MONTH($A1)&YEAR($A1),$Value)
From the documentation regarding SUMIF():
The SUMIF function returns the sum of a collection of numbers, including only numbers that satisfy a specified condition.
SUMIF(test-values, condition, sum-values)
- test-values: The collection containing the values to be tested. test-values can contain any value.
- condition: An expression that can include comparison operators, constants, the ampersand concatenation operator, and references. The contents of the condition must be such that the result of comparing the condition to another value results in the boolean value of TRUE or FALSE.
- sum-values: An optional collection containing the values to be summed. sum-values can contain number values, date/time values, or duration values. It should have the same dimensions as test-values.
EDIT (Complete revision):
Based on your data, the only way to accomplish this without redesigning the table is to create separate charts for each Segment shown. As each segment has 4 pieces of data, this is the only way to do this. (There may be someone out there who is a charting expert that could do much better, I'm sure)
First, as you have done is to make a Total chart:
Then crate separate charts, positioned together for the segments:
The charts can be edited for looks and data views in the Inspector but that is beyond the scope of the question.
Best Answer
A look at
SUMIFSandSUMIFin 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 istrue. 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)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
SUMIFSfunction, see the help documents.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 theThis month totalin the example below.Sample Table