SUMIF vs SUMPRODUCT

T

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.)
 
R

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top