Sumifs give zero result

[leprince2007]

Hello everybody,
Could you help me in my question ,please?
Sumifs formulas gives zero if there are multiple criteria.The formula is found in G7
Office version: Office 365
see the attachment

[Richard Buttrey]

Your formula is asking sumifs to return a value when a cell in column B contains BOTH F3 & F4 which is illogical.
Use instead

Please Login or Register  to view this content.

[Pete_UK]

You can do it like this in G7:

=SUMIFS($D$4:$D$17,$B$4:$B$17,F3)+SUMIFS($D$4:$D$17,$B$4:$B$17,F4)

or like this:

=SUM(SUMIFS($D$4:$D$17,$B$4:$B$17,{"Class A","Class B"}))

Hope this helps.

Pete

[Richard Buttrey]

If you're faced with a choice of summing everything APART from one criteria as here then

Please Login or Register  to view this content.

[avk]

"g7"

Please Login or Register  to view this content.

[Pete_UK]

Or you can have this in G7:

=G3+G4

Hope this helps.

Pete

[CARACALLA]

G7=SUMPRODUCT(SUMIF(B4:B$17,F3:F4,D4:D17))

another way

G7=SUMPRODUCT((ISNUMBER(MATCH(B$4:B$17,F3:F4,0)))*(D4:D17))

[leprince2007]

Thank you all for your solutions but I need to fix sumifs formula
Note: I need one sumifs formula

[Pete_UK]

...Note: I need one sumifs formula...

Why? Who dictates this constraint?

Pete

[leprince2007]

Sorry Mr.Pete_UK but I need to use sumifs formula
Why does it give zero?
This is my question ,not to find another solution

[Pete_UK]

Richard told you what was wrong with your existing formula in Post #2. SUMIFS is using an AND condition in its criteria, so you were trying to SUM column D IF the cells in column B equal both F3 AND F4, which clearly cannot be the case.

You want an OR condition, and you can achieve this by having two SUMIF terms added together. My two suggestions in Post #3 both show how this can be done.

Hope this helps.

Pete

[Richard Buttrey]

...and just to expand on Pete's post below, all criteria in a SUMIFS/COUNTIFS are mutually inclusive, and as more criteria are added you narrow down the number of results you'll get. As Pete says each criteria is an AND condition.

You are looking at OR conditions. i.e. a result which includes BOTH F3 or F4, hence you need to add two SUMIFS each of which contains one or other of the F3 or F4 values.

[leprince2007]

Thanks a lot for your replies
solved

[Pete_UK]

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete