sumproduct question

  • Thread starter Thread starter hansjhamm
  • Start date Start date
H

hansjhamm

I am having an issue with getting the sumproduct to return the
information I need.
I am looking down a column and summing all the data that does not state
"inactive" it can range from 0% to 100%

=SUMPRODUCT((B39:B416=B18)*(N39:N416>=0)*(N39:N416<>"Inactive"))

This part is correct, cause I am looking for 3 instances which it gives
me.

But where I am having the problem is this: of those 3 instances; I have
a 0%, 100% and a 100% which averages out at 66.67%. How do I take the
above formula and add in a part to average the actual and only the
numeric data?


Thanks!


Hans
 
=AVERAGE(IF((B39:B416=B18)*(N39:N416>=0)*(N39:N416<>"Inactive"),A39:A416))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Bob,

I circled around this, but never quite came up with your solution.
Never thought about average AND if!

Hey it works like a charm!!! As usual!!

Thanks Again for all of your help,

Hans
 
Or maybe
=SUMPRODUCT((B39:B416=B18)*(N39:N416>=0)*(N39:N416<>"Inactive")*(N39:N416))
/SUMPRODUCT((B39:B416=B18)*(N39:N416>=0)*(N39:N416<>"Inactive"))
The numerator sums while the denominator counts.
best wishes
 
Bernard,

Glad you posted this; I tried that very thing and never did get it to
work...Do you have any ideas why it didn't? I tried entering normally
and as an array

Hans
 
You need to check that you have real numbers
What did you get - wrong value or an error message?
Send me the file (to my private email not the newsgroup)
 
Back
Top