error in formula

[Ken G]

=SUMIF($Q$35:$Q$2000,A9)*SUM($H$35:$H$2000,H:H>0)

Where A9 is a code ..say W
Where col Q between Q35 and Q2000 looks for that code W
if that is TRUE, then I want it to sum all numbers in H35:H2000 where H:H>0
Where H>0..is the corresponding number on that particular row..

In other words..I want it to look at Q35:Q2000, if it sees a "W" I want it
to sum the value in col H (same row as it saw the W)if that number is >0

 

[Mike H]

Try this

=SUMPRODUCT(($Q$2:$Q$2000=A9)*($H$2:$H$2000))

Mike

 

[Rick Rothstein]

If the value in Column H is negative, your formula will not give the OP what
he wants (notice the H:H>0 requirement). You also changed the range the OP
wanted (which might be a source of confusion for him as well). This
modification to your formula should do what the OP wants...

=SUMPRODUCT(($Q$35:$Q$2000=$A$9)*($H$35:$H$2000>0)*$H$35:$H$2000)

 

[Ken G]

THE RESULT COMES BACK #VALUE

Try this

=SUMPRODUCT(($Q$2:$Q$2000=A9)*($H$2:$H$2000))

Mike

 

[Ken G]

HHMMM. COMES BACK #VALUE

If the value in Column H is negative, your formula will not give the OP what
he wants (notice the H:H>0 requirement). You also changed the range the OP
wanted (which might be a source of confusion for him as well). This
modification to your formula should do what the OP wants...

=SUMPRODUCT(($Q$35:$Q$2000=$A$9)*($H$35:$H$2000>0)*$H$35:$H$2000)

 

[muddan madhu]

may be this

Array function use Ctrl + Shift + Enter

=SUM(IF(Q35:Q2000=A9,IF(H35:H2000>0,IF(ISNUMBER
(H35:H2000),H35:H2000))))

 

[Rick Rothstein]

What kinds of values are in H35:H2000? Do you have any text in that range?
If so, try this version of the formula instead...

=SUMPRODUCT(($Q$35:$Q$2000=$A$9)*($H$35:$H$2000>0),$H$35:$H$2000)

 

[Ken G]

Thanks Rick..that seemed to work..No I did not have any text in that
column..but that formula worked

 

[Rick Rothstein]

Then you have formulas in that range the evaluate to the empty string ("")... the last formula I posted works against them the same way as it does for text entries.