Sum If Formula with 3 criteria

[Peanut]

I need to create an array formula with 3 criteria. I have done it with 2
before, but I can't seem to get it to work with 3. I have to use the Sumif
array instead of the sumproduct because I need to have blank cells within my
table.

This is essentially the formula I have, but all it returns is n/a.

=SUM(IF((A6:A500=A1)*(B6:B500=A2)*(C6:C500=A3),D6500=A4))

where column A is region, B is product, C is month of sale, and D is price.

I've looked in the archive questions, but I can't find anything similar that
doesn't use sumproduct.

Can anyone help me?

 

[Peo Sjoblom]

You are not summing, you are counting even though your formula is incorrect

=SUMPRODUCT(--(A6:A500=A1),--(B6:B500=A2),--(C6:C500=A3),--(D6500=A4))

will count but if you want to sum D6500 use

=SUMPRODUCT(--(A6:A500=A1),--(B6:B500=A2),--(C6:C500=A3),D6500)

if you want to sum D6500 but only those values equal to A4


=SUMPRODUCT(--(A6:A500=A1),--(B6:B500=A2),--(C6:C500=A3),--(D6500=A4),D6500)


--


Regards,


Peo Sjoblom

 

[Elkar]

Your example doesn't make sense. You've got 4 criteria, and nothing to sum.
Unless the D6500 portion is the sum column and the =A4 is a typo? Or are
you actually trying to do a COUNT?

I don't see any reason why SUMPRODUCT wouldn't work in this case.

=SUMPRODUCT(--(A6:A500=A1),--(B6:B500=A2),--(C6:C500=A3),D6500)

Blank cells would have no adverse effects on this formula.

However, if you still want to use SUM, then try this:

=SUM(IF(A6:A500=A1,1,0)*IF(B6:B500=A2,1,0)*IF(C6:C500=A3,1,0)*D6500)

Enter as an array. This option however, does cause a problem if a blank is
located in Column D.

HTH,
Elkar

 

[Peanut]

I am summing. I am summing the "price" or the sale according to the region,
product, and month. I have tried the sumproduct formula by myself as well as
exactly listed below - I can get it to work when I have the range exactly
what I have information available today, but as this worksheet is going to
grow, I can't keep modifying the formula to include a new line. I can't get
sumproduct to work when it is including blank cells - however, the sum(if(
array formula does work under those circumstances. Even though I need the
range to be from 6-500, my information is currently only 6-100.

I made a typo on the previous formula in giving the last item a criteria.
It should be like this:

=SUM(IF((A6:A500=A1)*(B6:B500=A2)*(C6:C500=A3),D6500))

 

[Peanut]

Nevermind. I figured it out on my own.

I am summing. I am summing the "price" or the sale according to the region,
product, and month. I have tried the sumproduct formula by myself as well as
exactly listed below - I can get it to work when I have the range exactly
what I have information available today, but as this worksheet is going to
grow, I can't keep modifying the formula to include a new line. I can't get
sumproduct to work when it is including blank cells - however, the sum(if(
array formula does work under those circumstances. Even though I need the
range to be from 6-500, my information is currently only 6-100.

I made a typo on the previous formula in giving the last item a criteria.
It should be like this:

=SUM(IF((A6:A500=A1)*(B6:B500=A2)*(C6:C500=A3),D6500))