SumIf with three criteria using cell values

  • Thread starter Thread starter dbaDonna
  • Start date Start date
D

dbaDonna

Hi!
I saw a tip to use SumProduct in order to use more than one criteria,
but it was using hard coded values:

=SUMPRODUCT ( (A1:A10="b") * (B1:B10="B") * (C1:C10) )

I want to use three criteria, and want it to replicate more easily with
changes, so I want to compare the value of, say, A1-A10 to the value in
F9; and the value of B1-B10 to the value in G10; and the value of
C1-C10 to the value in H11, ...

I've tried a few ways, but either get an error, or I get a value of 0
when it should be something else.

???
TIA
Donna
 
Hi Donna

=SUMPRODUCT ((A1:A10=F9)*(B1:B10=G10)*(C1:C10=H11)*(D1:D10))

or similar, should work for you.
 
Hi Donna,

The explanation of what you want to do doesn't match the
sample formula. Use this formula:

=SUMPRODUCT((A1:A10=F9)*(B1:B10=G10)*(C1:C10=H11))

This is more versatile than, as you point out, hard
coding.Probably the reason you got incorrect results is
that in the example formula you didn't have a logical
equated with the range C1:C10. As written, that formula
would have tried to SUM the values in C1:C10 as opposed to
COUNTING them if I follow you correctly.

Biff
 
Back
Top