Why this not work
SUMPRODUCT((A$1:A$7="FIN")*(A$1:A$7="OVH")*(B$1:B$7))??
Dear Expert,
I have data as below
A B
FIN 10
OVH 20
COS 30
FIN 40
OVH 50
COS 60
FIN 70
thanks i need only SUMPRODUCT
could you rectify my formula?
I think it doesn't work because you give two criteria on the same column. If you evaluate the formula, you'll see that it finds the first criterium (FIN) (with true and falses) and it finds the second one also. But when it combines both everything turns into zero's. When you only use one criterium in this column it works perfect. I think it's a function where you can use more criteria but in different columns.
Hi Mey,
Try this:
=SUMPRODUCT(((A$2:A$8="FIN")+(A$2:A$8="OVH"))*(B$2:B$8))
You need to change the AND operator * between your criteria to the OR operator +.
More on how SUMPRODUCT works here: SUMPRODUCT as an alternative to SUMIFS function
Mynda
Learned something again Mynda! Thanks!
🙂 my pleasure.
