SUMIF vs SUMPRODUCT

[Tim Richards]

I was using the SUMIF command as follows (=SUMIF('Active Data'!Z:Z,2,'Active
Data'!AK:AK) ("Active Data" is the previous tab), and the formula works
great!.... until the criteria for the calulation changed.
Now I require 2 sets of criteria before I sum the AK:AK column.
I changed the formula to(SUMPRODUCT(('Active Data'!Z2:Z1000=2)*('Active
Data'!BE2:BE1000=1)*('Active Data'!AK2:AK1000)).
The additional data is coming from column BE.
Apparently the SUMPRODUCT doe not like my referencing the Active Data tab.
This formula returns the ever popular #VALUE! answer.
Column Z is the month code (Feb), column BE is a customer code (1,0 or -1),
and column AK repersents the hours of service.
The formula should result all the hours worked(AK) in Feb(Z) for all the
customers with criteria "1" (BE). (in this case 408 hours worked.)

 

[Ron Coderre]

I suspect this variation of the SUMPRODUCT structure
(in sections) will work for you:

=SUMPRODUCT(--('Active Data'!Z2:Z1000=2),--
('Active Data'!BE2:BE1000=1),('Active Data'!AK2:AK1000))

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[T. Valko]

There's nothing wrong with your formula. However, a better way to write it
would be:

=SUMPRODUCT(--('Active Data'!Z2:Z1000=2),--('Active
Data'!BE2:BE1000=1),'Active Data'!AK2:AK1000)

Your error is more than likely coming from column AK which might contain
TEXT. Using the form of SUMPRODUCT that your are will result in a #VALUE!
error. The above form of SUMPRODUCT will ignore any TEXT entries in column
AK.

Also, if you have any #VALUE! errors already in *any* of the ranges you'll
get the #VALUE! error.