sumifs formula not working as expected

[Mr2017]

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")

[TABLE="width: 311"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Checked or Partially Checked? [/TD]
[TD]Total Units[/TD]
[/TR]
[TR]
[TD]Checked[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Partially Checked[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Partially Checked[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Checked[/TD]
[TD="align: right"]10

[/TD]
[/TR]
</tbody>[/TABLE]

 

[tyija1995]

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

 

[Mr2017]

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]

Assuming that there are no other possibilities ending in "Checked" that you do NOT want to count, you could use a wildcard like this:

=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]

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.