How to sumif for multiple criteria in a single column?

[ChrisTheWizard]

 

As an example. I want to sum everything in D column for this financial year with the Description of "Taxi" and "Cash"

=SUMIFS(Data!D:D,Data!A:A,">="& DATE(YEAR(TODAY()),7,1),Data!A:A,"<=" & DATE(YEAR(TODAY())+1,6,30),Data! E:E,"Cash",Data!E:E,"Taxi")

 This returns 0. Is it because I need to add multiple sumifs statements together or am I missing something in my statement?

[#2 · Accepted solution]

@ChrisTheWizard The screenshot suggests that you are on using the mobile version of Excel. Then the attached example, created on the desktop, should work for you as well.

No doubt, other will contribute alternative solutions.

[#3]

@ChrisTheWizard The SUMIFS function restricts you to AND logic of the criteria. To use OR, you can switch to using the SUM function. Here is a simplified example summing column A only if column E equals Cash OR Taxi. 

=SUM(Data!A:A*((Data!E:E="Cash")+(Data!E:E="Taxi")))

[#4]

Not quite

Trying to sum D but

=SUM(Data!D:D*((Data!E:E="Cash")+(Data!E:E="Taxi")))

returns #value. 

[#5]

How to do from a separate sheet. For example data sheet is called "Data".

Also on mobile it doesn't like the formula. I'll try adding it on desktop first. The spreadsheet you attached works fine but If I copy and paste the formula... It's like some of the characters go across all weird.

[#6]

@ChrisTheWizard 

When you use a structured table (like in my example) it doesn't matter where the data sits in your workbook. Excel will find it by the table name. If you can't (or don't want to) use a structured table, you can replace the table/column references with absolute sheet/cell references.

With regard to @JKPieterse 's formula, he merely tried to demonstrate that you need to sum the results of two formulas. One for Taxi and one for Cash. Tried it and it works. However, you still want to include the condition that look at the financial year from July 1, 2024 to June 30, 2025. and then it becomes messy.

Based on my example, you would want to use two separate FILTER functions and SUM these. It would then be:

=SUM(
FILTER(Table1[Cash],(Table1[Date]>=DATE(YEAR(TODAY()),7,1))*(Table1[Date]<=DATE(YEAR(TODAY())+1,6,30))*(Table1[Description]="Taxi")),
FILTER(Table1[Cash],(Table1[Date]>=DATE(YEAR(TODAY()),7,1))*(Table1[Date]<=DATE(YEAR(TODAY())+1,6,30))*(Table1[Description]="Cash"))
)

But, with all that repeating code, LET would be more efficient, I believe.

If you can't get it to work, perhaps best if you share a link giving full access to your own file, stored on OneDrive, Dropbox or similar.

[#7]

I decided on LET but my phone keeps saying "There's a problem with this formula."

https://1drv.ms/x/s!AqSkT5cvQtioryF-mGEhgNa6pDAw

Here's the url to my OneDrive file. I have completely rewritten this in excel vs copy and paste so shouldn't be any invalid characters.

What am I doing wrong?

Edit: I wrote an apostrophe in front of the formula so I can show you.

[#8]

Several things.
- The data on the second sheet needs to be formatted as Table (Format as Table from the Home tab)
- There is a trailing space after "Description" on the second tab
- The formula has a couple of closing parentheses in the wrong place, I fixed those for the yellow cell.

[#9]

Thank you so much for the reply and now I know for future projects. Cheers. 😁

[#10]

Marked as best response because most efficient method for this thread. I'm sure many others will find it useful 👍