SumIfs Question

Ezguy4u

Active Member
Joined
Feb 10, 2010
Messages
336
Office Version
  1. 365
Platform
  1. Windows
Why am I getting a 0 when I use more than one criteria on this SumIfs function. The problem is very simple but it still should work. Instead of 0 I should get 17.

20-08-13 SumIfs.xlsx
AB
1A1
2B2
3B3
4A4
5A5
6B6
7C7
8
9AC
100
1110
127
1310
147
150
Sheet1
Cell Formulas
RangeFormula
A10A10=SUMIFS(B1:B7,A1:A7,A9,A1:A7,B9)
A11A11=SUMIFS(B1:B7,A1:A7,A9)
A12A12=SUMIFS(B1:B7,A1:A7,B9)
A13A13=SUMIFS(B1:B7,A1:A7,"A")
A14A14=SUMIFS(B1:B7,A1:A7,"C")
A15A15=SUMIFS(B1:B7,A1:A7,"A",A1:A7,"C")
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
=SUM(SUMIFS(B1:B7,A1:A7,{"A","C"}))
either that or try braking the sum ifs
=SUMIFS(B1:B7,A1:A7,A9)+SUMIFS(B1:B7,A1:A7,B9)
someone else might be able to come up with a more elegant solution though.
 
Upvote 0
You have to use SUM with sum if and press Ctrl+Shift+Enter and not just enter

Book1
ABC
1A1
2B2
3B3
4A4
5A5
6B6
7C7
8
9AC
1017
11
12
Sheet1
Cell Formulas
RangeFormula
A10A10=SUM(SUMIFS(B1:B7,$A$1:$A$7,$A$9:$B$9))
 
Upvote 0
In C9 implement one of:

=SUMPRODUCT(SUMIFS($B$1:$B$7,$A$1:$A$7,$A9:$B9))

=SUM(SUMIFS($B$1:$B$7,$A$1:$A$7,$A9:$B9)) [ apply control+shift+enter if necessary ]

=SUM(SUMIFS($B$1:$B$7,$A$1:$A$7,{"A","C"})) [ cannot be copied down ]

=SUMPRODUCT(SUMIFS($B$1:$B$7,$A$1:$A$7,CHOOSE({1,2},$A9,$B9)))

Preference should be with the first option.
 
Upvote 0
Question I like your second suggestion. Thank You. CA your suggestion just needs the cse and Thank You. Aladin really brought it home with some good suggestions. I am going to copy all of these into my Sumifs examples.

I still have to wonder why we have to use other functions to get this Sumifs to work for this specific problem. I am using Office 2019 so Excel version 2019. Is there something I am missing to get Sumifs to work properly? Is there some rule where you can only use 1 Text for a criterion? Topic for conversation.
 
Upvote 0
Feeding SUMIFS multiple values to test a range forces it to create for each value a result, so SUMIFS generates multiple totals. We need then to sum these totals, hence the enclosing SUM or SUMPRODUCT which designed to aggregate multiple totals (numbers) into a single number. Putting it differently, SUMIFS is not designed to totalize multiple totals it is forced to create.
 
Upvote 0
Very good Aladin. This does give me something to think about. I wonder if “SUMIFS is not designed to totalize multiple totals it is forced to create.” leads into my next thoughts.

As I look at this Sumifs example and by the way this is just academic, I have to wonder if trying to use multiple criteria in the same array column is the problem. In other words, if you need to use multiple criteria from the same array column then you need to use other functions or techniques with Sumifs.

For me it seems intuitive that I can use multiple criteria from the same array column. But as I review all the information above I see that is not the case.
 
Upvote 0
IN REPLY TO POST #7 (A try to help you with your thought, Hoping I can)

Sumifs can be used for multiple criteria in the same array column, PROVIDED all the criteria turns to be true for the output to come. I mean the multiple criteria used within Sumifs, should all be true together to give you the desired result.

In the formula you have used to get the output, all are providing the correct result except the that used in cell A10 and A15, because the criteria you have used within the sumifs can't be true at the same time. Even the formula

=SUMPRODUCT(SUMIFS(B1:B7,A1:A7,A9,A1:A7,B9))

will give you zero as a result, because as I mentioned two criteria can't be true at the same time in this data, if cell contains "A", it can't contain "C".

In short Sumifs treat the criterias as within AND operation.

However as mentioned by Aladin, the formula used by him, treats both criteria as one, an array is being used and so as mentioned by him, two inputs are fed to sumifs together and later sumproduct is used to do the total of the two inputs.

If I am understanding wrong then more clarifications are welcome.
 
Upvote 0
Well isn’t this a lively discussion. I am so glad we can have an exchange of ideas on this wonderful web site. So ER I hope I can clear up some of the in-depth processes we are disseminating here. And let me just reiterate, Aladin solutions are amazing. Using those functions and techniques were genius, As I look at your writing, I would need some clarification on “PROVIDED all the criteria turns to be true for the output to come.” It doesn’t make any sense to me how criteria turns to be true. Can criteria turn? And the statement “if cell contains "A", it can't contain "C".” I am not sure what you are saying if you are looking at the original problem. The cells have either an A in them or a C in them. None of the cells have both an A and a C.

ER let me try this tack. Your boss Jeff Bezos wants you to add up the employees from company A and C. Ignore company B. He has given you his personal excel spreadsheet with this information. Company names are listed in column A and number of employees are listed in column B. Jeff has told you to only use the Sumifs function because as he understands multiple criteria, as Sumifs looks at each individual cell and sees an A or a C then it should add the number to the total.

But you are going to have to tell your boss that can’t be done because as Ezguy has stated “You cannot use multiple criteria from the same array column when using Sumifs only.” Your going to have to tell Jeff, I know as you look at the data and see an A you should get that number from column B. And as you look at column A and see C company you should get that number in column B. We have to use other functions and techniques with the Sumifs.

So ER maybe your understanding was a little off the mark but thank you for your time
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top