Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Sumifs Multiple Conditions

Hello.


I would like to use sumifs to calculate items based on date range and category.


I have the following tables:

User uploaded file

User uploaded file

User uploaded file


In the Actual Cell for category 1, I want to add up all values that are in that category for the month entered in the top table. I can get the sumif to work with just category, but I can't add the second condition with dates to work.


The formula I have so far is:


SUMIFS(Transactions::C2:C11,Transactions::D2:D11,$A2,Transactions::A2:A11,">=1/1 /17")+'Income-1'::$A2)

Posted on Jan 1, 2018 6:51 PM

Reply
3 replies
Sort By: 

Jan 2, 2018 12:01 AM in response to spritelhs

SUMIFS(Transactions::C2:C11,Transactions::D2:D11,$A2,Transactions::A2:A11,">=1/1 /17")


Your formula, as written (ignoring the fixed "+Income::$A2" at the end), would (if it were working) sum all the in category 1 on any date after the first of January 2017. In other words, every category 1 amount for all of 2017 and any 2018 category expense recorded this year.


If your Transactions table is going to record only the transactions occurring in a single year, SGIII's suggestion of adding a column to contain the monthname of each transaction in that table will work. If you will continue to use the same transaction table through a second year (or more), you'll need to include the year in that column as well.


Any solution is going to require that the values in Transactions::A are 'date & time' values, not 'text' values. The value in A2 of the table at the top should be Text for the example below. The year in B2 of that table should be a number. Both of these can be chosen from pop-up menus in the cells, or entered directly.


Example:

User uploaded file

Month:

Row 1 contains only labels.

A2 and B2 contain the Monthname and Year respectively. These are entered directly, either by hand or using a pair of pop-up menu cells.


C2: A2&" "&B2

The formula concatenates the monthname and the year, inserting a single space between them. Column C of this table is needed only for the calculations, and can be hidden.


Summary:

Column A contains a list of categories, entered directly.

Column B contains the monthly Budget amounts for each category, entered directly.

Column C contains the formula below the table, entered in C2 and filled down to C4.


C2: SUMIFS(Transactions::C,Transactions::D,A2,Transactions::E,Month::$C$2)


Column D contains the formula below, entered in D2 and filled down:


D2: B2−C2

If you want 'over spending' to show as a positive value and 'under spending' to show as a negative value, reverse the order to C2-B2


Transactions:

Column A contains the date&time value displaying only the date for each transaction. If only the date is entered, Numbers automatically sets the time part to 00:00:00 (midnight at the start of that day). The date part may be formatted as you wish.


Column B plays no part in the calculations, and has been left blank.


Column C contains the amount of each transaction. In practice, I would set the format to number, with two places after the decimal. The user knows what currency he/she is using, and does not need all those currency symbols cluttering each column. YMMV


Column D contains the formula below, entered in D2 and filled down to the end of column D. The core formula, shown in bold, extracts the month(number) and the year from the date in column A, converts the month to the monthname, then concatenates the monthname, a space, and the year to match the format used in Month::C2. The part not in bold is a 'switch' that prevents calculation until there is a value entered in that row of column A.


D2: IF(LEN(A2)<1,"",MONTHNAME(MONTH(A2))&" "&YEAR(A2))


This column is used only for the calculations in Summary, and may be hidden.


Regards,

Barry

Reply

Sumifs Multiple Conditions

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.