sumproduct not working

  • Thread starter Thread starter antonio
  • Start date Start date
A

antonio

I'm trying to add all the number in the raw data sheet
that meet certain criteria.

The formula I am using is

=SUMPRODUCT(('Raw Data'!$AK$2:$AK$9938=B9)*('Raw Data'!
$G$2:$G$9938=$D$6)*(ISNUMBER(MATCH('Raw Data'!
$F$2:$F$9938=$Q$1:$Q$5,0))),'Raw Data'!$O$2:$O$9938)

The formula was working fine when f2:f9938 was set equal
to one cell.( for example Q1)

But know I need the formula to look at the range Q1:Q5

So if any of the number in cells f2:f9938 are equal to any
of the number in range q1:q5 to add them up. Those
numbers are in cell o2:o9938.

Any ideas.

I know I could break up this formula in to 5 different
formulas, but I want to know if there is a way to avoid
all that?
 
Ant, antonio, antonia, whatever-

Why not try it on a smaller range than 9936 rows to convince yourself that
it works?
 
one way is to write an array formula with the 5 conditions

=SUM(IF(F2:F9938=Q1,O2:O9938,IF(F2:F9938=Q2,O2:O9938,IF(F2:F9938=Q3,O2:O9938
,IF(F2:F9938=Q4,O2:O9938,IF(F2:F9938=Q5,O2:O9938,0))))))

enter the above while holding cntrl and shift buttons

Regards
Bill K
 
I'm trying to add all the number in the raw data sheet
that meet certain criteria.

The formula I am using is
[reformatted]
=SUMPRODUCT(('Raw Data'!$AK$2:$AK$9938=B9)
*('Raw Data'!$G$2:$G$9938=$D$6)
*(ISNUMBER(MATCH('Raw Data'!$F$2:$F$9938=$Q$1:$Q$5,0))),
'Raw Data'!$O$2:$O$9938)

The formula was working fine when f2:f9938 was set equal
to one cell.( for example Q1)
Click to expand...

The MATCH call shouldn't produce anything useful. As written,

MATCH('Raw Data'!$F$2:$F$9938=$Q$1:$Q$5,0)

has only 2 arguments, 'Raw Data'!$F$2:$F$9938=$Q$1:$Q$5 and 0. All this produces
is an array of #N/A errors, so wrapping it inside ISNUMBER should produce an
array of all zeros. Is the equal sign, =, a typo? If so, that's your problem.
But know I need the formula to look at the range Q1:Q5

So if any of the number in cells f2:f9938 are equal to any
of the number in range q1:q5 to add them up. Those
numbers are in cell o2:o9938.
Click to expand...

=SUMPRODUCT(('Raw Data'!$AK$2:$AK$9938=B9)
*('Raw Data'!$G$2:$G$9938=$D$6)
*(COUNTIF($Q$1:$Q$5,'Raw Data'!$F$2:$F$9938)>0),
'Raw Data'!$O$2:$O$9938)

if you need to use as few levels of nested function calls as possible, or

=SUMPRODUCT(('Raw Data'!$AK$2:$AK$9938=B9)
*('Raw Data'!$G$2:$G$9938=$D$6)
*(ISNUMBER(MATCH('Raw Data'!$F$2:$F$9938,$Q$1:$Q$5,0))),
'Raw Data'!$O$2:$O$9938)

otherwise. SUMPRODUCT works just fine.
 
Harlan Grove said:
I'm trying to add all the number in the raw data sheet
that meet certain criteria.

The formula I am using is
[reformatted]
=SUMPRODUCT(('Raw Data'!$AK$2:$AK$9938=B9)
*('Raw Data'!$G$2:$G$9938=$D$6)
*(ISNUMBER(MATCH('Raw Data'!$F$2:$F$9938=$Q$1:$Q$5,0))),
'Raw Data'!$O$2:$O$9938)
Click to expand...
Click to expand...
array of all zeros. Is the equal sign, =, a typo? If so, that's your problem.
Click to expand...


=SUMPRODUCT(('Raw Data'!$AK$2:$AK$9938=B9)*('Raw Data'!$G$2:$G$9938=$D$6)*
(ISNUMBER(MATCH('Raw
Data'!$F$2:$F$9938,$Q$1:$Q$5,0))),'RawData'!$O$2:$O$9938)

was what i provided him when he called himself "Ant", and it works fine. Why
he put a = in place of a , I don't know.
 
True. To make it foolproof (I thought), I entered it right into his formula
so he could just cut n' paste it. I can see needing to re-type if someone
would have said "use something like ISNUMBER(MATCH(A1:A10,B1:B10,0))" ..

Though I'm not surprised, most people I see 'computing' at work are not
familiar with CTRL-C and CTRL-V.
 
Back
Top