Sumproduct formula won't calculate...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi al

I want to sum a group of non sequential cells that meet two sets of criteria, then average the resul

I started with this formula to get the sum - but it's not working
=SUMPRODUCT(($A$13:$A$103=$A$13),(H13:H103>1)),(C13:C103)

A13 = "TV1". I only want to include rows that have column A matching what the user has entered into cell A1
Then I only want to include cells in H13:H103 that are greater than 1 or zero
then I want it to total the values in C13:C103 based on meeting the prior two criteri
and lastly I need an average of the above result based on the same criteria

If I have a formula in all H cells will that affect it? The formula is basically telling the result to show as blank if error messages appear in F13.: if(isnumber(F13),C13/D13,""

Any help would be appreciated
 
Hi BeSmart!

Not far off the right answer. Here's what you need for your starting
formula:

=SUMPRODUCT(--($A$13:$A$103=$A$13),--(H13:H103>1),(C13:C103))

First there was a parenthesis change.
Second you need the -- before the implicit IF statements to coerce the
returns of TRUE and FALSE to 1 and 0

You could also use:

=SUMPRODUCT(($A$13:$A$103=$A$13)*(H13:H103>1)*(C13:C103))

The multiplication of the statements forces the coercion to 1 and 0.

To get your average use:

=SUMPRODUCT(--($A$13:$A$103=$A$13),--(H13:H103>1),--(C13:C103))/SUMPRODUCT(-
-($A$13:$A$103=$A$13),--(H13:H103>1))
--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

BeSmart said:
Hi all

I want to sum a group of non sequential cells that meet two sets of
Click to expand...
criteria, then average the result
I started with this formula to get the sum - but it's not working:
=SUMPRODUCT(($A$13:$A$103=$A$13),(H13:H103>1)),(C13:C103))

A13 = "TV1". I only want to include rows that have column A matching what
Click to expand...
the user has entered into cell A13
Then I only want to include cells in H13:H103 that are greater than 1 or zero.
then I want it to total the values in C13:C103 based on meeting the prior two criteria
and lastly I need an average of the above result based on the same criteria.

If I have a formula in all H cells will that affect it? The formula is
Click to expand...
basically telling the result to show as blank if error messages appear in
F13.: if(isnumber(F13),C13/D13,"")
 
Try:

=SUMPRODUCT(--($A$13:$A$103=$A$13),--(H13:H103>1),(C13:C103))

When you write =sumproduct() this way, you'll need to convert each term to a
number.

the -- stuff does two things. First, it changes true/false to -1/0, then the
second one changes the sign back to positive.

(And watch your parentheses!)
 
Back
Top