# sumifs formula not working as expected

#### Mr2017

##### Active Member
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!

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

 Checked or Partially Checked? Total Units Checked 10 Partially Checked 10 Partially Checked 10 Checked 10

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

#### tyija1995

##### Well-known Member
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
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
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
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: