what is wrong with this formula

  • Thread starter Thread starter Norbert
  • Start date Start date
N

Norbert

=SUMPRODUCT((A1:A21=D1)*(B1:B21),(F1:F7))/SUMPRODUCT(--(A1:A21=D1),--(B1:B21))

results in #VALUE!

#WHY?
 
Norbert said:
=SUMPRODUCT((A1:A21=D1)*(B1:B21),(F1:F7))
/SUMPRODUCT(--(A1:A21=D1),--(B1:B21))
results in #VALUE! #WHY?
Click to expand...

One reason: because the ranges A1:A21, B1:B21 and F1:F7 are not all the
same size.

Another possible reason: If "F1:F7" is a typo and you mean F1:F21, because
some cells in B1:B21 contain text, not numbers or boolean values (true,
false).

If B1:B21 contains boolean values (true, false), then the formula can be
rewritten as follows:

=SUMPRODUCT(--(A1:A21=D1),--B1:B21,F1:F21)
/SUMPRODUCT(--(A1:A21=D1),--B1:B21)

If you want to consider only numeric values in B1:B21, then use one of the
following formulas(equivalent):

=SUMPRODUCT(--(A1:A21=D1),B1:B21,F1:F21)
/SUMPRODUCT(--(A1:A21=D1),B1:B21)

or

=SUMPRODUCT(--(A1:A21=D1),B1:B21,F1:F21)
/SUMIF(A1:A21,D1,B1:B21)

The double negation ("--") is needed only to convert boolean values (true,
false) to numbers (1, 0).
 
Back
Top