sumifs formula not working as expected

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
458
Hi

I've got two columns of data (A and B) and I'd like to sum the data in column B that is either "Checked" or "Partially Checked" in column A - please see the small sample below. The sum should be 40, in this case.

I've used this formula, but it's not returning the expected result. Can anyone please clarify what needs to be changed? I've done this kind of thing before, but it's been a while!

Thanks in advance.

=SUMIFS(B:B,A:A,"Checked",A:A,"Partially Checked")

Checked or Partially Checked? Total Units
Checked10
Partially Checked10
Partially Checked10
Checked10


<colgroup><col><col></colgroup><tbody>
</tbody>
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
Hey,

Put it as a sum:

=SUMIFS(B:B,A:A,"Checked")+SUMIFS(B:B,A:A,"Partially Checked")

=SUMIF(A:A,"Checked",B:B)+SUMIF(A:A,"Partially Checked",B:B)

Both formulas above will work as there is only technically 1 criteria each time, so SUMIF will work as well as SUMIFS.

EDIT: The reason your formula doesn't work properly is because you are saying that column A must equal "Checked" AND "Partially Checked" - which is impossible
 
Last edited:

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
458
Ok, thanks tyija1995.

I believe there's a way to do it without having to use the '+' sign to add the additional conditions.

But I'll go with your suggestion for now.

Thanks for posting it.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
Assuming that there are no other possibilities ending in "Checked" that you do NOT want to count, you could use a wildcard like this:
Code:
=SUMIF(A:A,"*Checked",B:B)
Note that the reason why your original formula did not work because when using SUMIFS, the multiple conditions are treated as AND statements, not OR statements, meaning that ALL of the conditions must be met for the item to be counted. Obviously, it is not possible for a single value to equal EXACTLY "Checked" and EXACTLY "Partially Checked" at the same time. It is either one or the other.

The formula above that I posted says to count anything ending in "Checked".
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,604
Office Version
2010
Platform
Windows
Another alternative (normally entered; just press Enter, as usual):

=SUM(SUMIFS(B2:B1000,A2:A1000,{"Checked","Partially Checked"}))

Note the restricted ranges. In general, it is unwise to use whole-column ranges. In some cases, Excel looks at all 1+ million rows.
 
Last edited:

Forum statistics

Threads
1,085,544
Messages
5,384,362
Members
401,889
Latest member
Pmccollin

Some videos you may like

This Week's Hot Topics

Top