problem with sumproduct

  • Thread starter Thread starter Tom G
  • Start date Start date
T

Tom G

I am hoping that this will be my last problem today!

I am using

=SUMPRODUCT(--(GRADE=11),--(HS_TYPE=1),A_D)

i.e. trying to sumproduct when grade=11 and hs_type = 1.

the problem with this formula is that when HS_TYPE=1, there is no GRADE =
11. So, this should show up as #DIV/0.
This a way that I check to see if the formula is working.

alternatively, =SUMPRODUCT(--(GRADE=7),--(HS_TYPE=1),A_D) , works fine.

Can anyone spot the problem here? By the way, I know that when hs_type = 1,
there is no grade 10, 11, or 12.

Tom
 
Hi Tom
in your first case (Grade=11, HS_Type=1) SUMPRODUCT will NOT return a
#DIV/0 error (why should it) It will return 0 as there is no matching
entry and therefore there's nothing to sum

So IMHO the formula works just fine
 
Using an error to "check to see if the formula is working" is, IMO, not
a good idea.

There are usually a number of reasons that a particular error could
occur, and assuming a formula is working because an error is received is
usually no better than assuming a formula is working because a numeric
value is received.

IMO, it's much better to design your formula to work for your data, only
returning errors if an unexpected error occurs. For instance, based on
your description (I'm assuming you substutite range references for your
target GRADE and HS_TYPE - i.e., that you don't deliberately code an
error), I might use:

=IF(AND(D1=1, D2>=10), "HS Type 1: No Grades 10-12",
SUMPRODUCT(--(GRADE=D2),--(HS_TYPE=D1),A_D))
 
Back
Top