3rd variable in SUMPRODUCT formula

[scaffdog845]

Afternoon all,

I trying to use SUMPRODUCT to return a sum of hours in $K1:$K1000 based on two other sets of criteria. The formula is working fine with the first two sets of criteria, in this case returning the number "8". After trying to add the third criteria the formula is still returning "8" when it shoud be returning 374. Any ideas??

Please Login or Register  to view this content.

[daddylonglegs]

That formula you posted doesn't sum anything, it's a count of rows that meet the criteria, if you want to sum column K change to this

=SUMPRODUCT(('ZGWOS '!$H$1:$H$1000="BREAK IN - SCHEDULE")*('ZGWOS '!$N$1:$N$1000={"JJW BM","JJW CAD","JJW EL","JJW EQ","JJW IW","JJW LAB","JJW PF"})*('ZGWOS '!$K$1:$K$1000))

[scaffdog845]

Thanks DLL

When I used your replacement suggestion I received a #VALUE! error. I attempted another modification and received an incorrect value. Could you check out the attachment and see what looks wrong?

The JJ White numbers sheet is where I would like the formula to be in C2:C16. These cells currently have the correct results manually entered, and the ZQWOS sheet is currently filter to show the criteria and values that generate the results in row 2:2 on JJ White numbers.

Many thanks!!!....

[NBVC]

You get the #VALUE error because row 1 contains text strings.. and multiplying arrays causes errors when including a summing category.....

Try:

Please Login or Register  to view this content.

[scaffdog845]

You get the #VALUE error because row 1 contains text strings.. and multiplying arrays causes errors when including a summing category.....

Such a stupid thing to overlook. Thanks for spotting it! Problem solved....