Countifs Functions not adding up.

[joshuadalrymple]

Hello,

I have several countifs functions looking at the same data in order to pull totals.

The first equation:

=COUNTIFS(Data!$O:$O, "5 Pack",Data!$L:$L, "Plastic")

Gives me the total of 1000.

=COUNTIFS(Data!$O:$O, "5 Pack",Data!$L:$L, "Glass")

Gives me the total of 43.

The function =SUM(B3:B4)*5 gives me a grand total of 5215.

Now, I also need to know the flavors that were sold in the 5 packs, not just the size. So I am using this function.

=COUNTIFS(Data!$O:$O,"Build Your Own 5 Pack",Data!$G:$G,H66)+COUNTIFS(Data!$O:$O,"5 Pack",Data!$H:$H, H66)+COUNTIFS(Data!$O:$O,"5 Pack",Data!$I:$I,H66)+COUNTIFS(Data!$O:$O,"5 Pack",Data!$J:$J,H66)+COUNTIFS(Data!$O:$O,"5 Pack",Data!$K:$K,H66)

As I am sure you can tell, in the data worksheet, I have 5 columns where the name of the flavor would be populated, and a column where the product name, in this case 5 pack is populated. This function is intended to count an occurrence of a flavor in each of the 5 columns, only if it is a 5 pack, and give me the sum of the counts of all 5 columns.

I have 86 Flavors and 86 cells with this function. I then sum their answers using =SUM(I2:I86) and get 5205.

Clearly something is wrong, and I have spent more time than I am willing to admit trying to figure it out. I have manually counted and 5215 is the correct answer. However, I have also manually counted each flavor and I cannot find an error there either.

If someone could please lend me a hand that would be greatly appreciated.

 

[Chrisdontm]

You say there are 86-flavors...
You are adding 85-flavors...

 

[joshuadalrymple]

You say there are 86-flavors...
You are adding 85-flavors...

Yeah, I saw that. I have 85 flavors. Thanks.

I was able to figure it out. I was not accounting for orders with errors in which no flavor was selected. By adding another line item with the following function, I got it working.

=COUNTIFS(Data!$O:$O,"Build Your Own 5 Pack",Data!$G:$G,"")+COUNTIFS(Data!$O:$O,"Build Your Own 5 Pack",Data!$H:$H, "")+COUNTIFS(Data!$O:$O,"Build Your Own 5 Pack",Data!$I:$I,"")+COUNTIFS(Data!$O:$O,"Build Your Own 5 Pack",Data!$J:$J,"")+COUNTIFS(Data!$O:$O,"Build Your Own 5 Pack",Data!$K:$K,"")

Thanks!