if and sumproduct

[RichardO]

Hello everyone:

I am really grateful we have such a wonderful excel forum. Thanks fo
all your help.

I have a problem in excel. I would like to find the AVERAGE of th
numbers in column E if the contents of column J is anything other tha
SCI, Code, A/P and Blank i.e."", AND the contents of column M is Yes.
I have the following formula but it's not giving me the correc
answer.


=IF(SUMPRODUCT(('Raw Data'!$J$1:$J999<>"SCI")*('Ra
Data'!$J$1:$J$999<>"Code")*('Raw Data'!$J$1:$J$999<>"A/P")*('Ra
Data'!$J$1:$J$999<>"")*('Raw Data'!$M$1:$M$999="Yes")),AVERAGE('Ra
Data'!$E$1:$E$999))

Thanks a lot.


RichardO

 

[Frank Kabel]

Hi
try (the monster)
SUMPRODUCT(('Raw Data'!$J$1:$J999<>"SCI")*('Raw
Data'!$J$1:$J$999<>"Code")*('Raw Data'!$J$1:$J$999<>"A/P")*('Raw
Data'!$J$1:$J$999<>"")*('Raw Data'!$M$1:$M$999="Yes"),'Raw
Data'!$E$1:$E$999)/SUMPRODUCT(('Raw Data'!$J$1:$J999<>"SCI")*('Raw
Data'!$J$1:$J$999<>"Code")*('Raw Data'!$J$1:$J$999<>"A/P")*('Raw
Data'!$J$1:$J$999<>"")*('Raw Data'!$M$1:$M$999="Yes"))

 

[Dave R.]

You don't need IF and sumproduct in this case. The reason you are
multiplying your criteria together is to use the value 1 when they are all
true, in doing some other calculation. If any of your criteria are false,
one part of your multiplication (product) will be 0, so the value of the
calculation will be 0.

=SUMPRODUCT((E11:E14<>"Bill")*(F11:F14<>"Smith"),G11:G14)/SUMPRODUCT((E11:E1
4<>"Bill")*(F11:F14<>"Smith"))

Something like that would work, tailored to your needs, cumbersome as it is.
The comma in sumproduct seperates arrays to be mutliplied; so the first 2
criteria are evaluated (they will be either 1 if both are true, or 0 if
either is false), then multiplies it by the values in G11:G14. You can then
divide by the number that meet the first 2 criteria, and you'll have an
average.

 

[Ken Wright]

With an = in front obviously

SUMPRODUCT((RngB="Yes")*(RngA<>"SCI")*(RngA<>"A/P")*(RngA<>"Code")*(RngA<>"")*(R
ngVals)) /
SUMPRODUCT((RngB="Yes")*(RngA<>"SCI")*(RngA<>"A/P")*(RngA<>"Code")*(RngA<>""))

 

[Aladin Akyurek]

=AVERAGE(IF((1-ISNUMBER(MATCH('Raw
Data'!$J$1:$J999,{"SCI","Code","A/P"},0)))*('Raw Data'!$J$1:$J999<>"")*('Raw
Data'!$M$1:$M$999="Yes"),'Raw Data'!$E$1:$E$999))

which must be confirmed with control+shift+enter instead of just with enter.

 

[RichardO]

Thanks Frank, your formula worked. I'll try out the other formula
suggested. Thanks guys.


RichardO

 

[Dave R.]

Frank the omnipresent?

 

[Dave R.]

LOL. for some reason frank's message at 8:02 didn't show up here until after
all the other replies even one at 8:39, so I didn't know Frank had even
answered this question!

Frank the omnipresent?

 

[Harald Staff]

LOL. for some reason frank's message at 8:02 didn't show up here until after
all the other replies even one at 8:39, so I didn't know Frank had even
answered this question!

He always do, so always assume he did regardless of what internet tells you
<g>
(Have no idea how he does it though. He produced the same amount of postings
this february that I did in 2003. Whole year, that is.)

Best wishes Harald

 

[Frank Kabel]

Hi Harald
quite simple: Currently boring job with nothing to than to watch/check
some batch procedures (lets call this on 4th level support if something
strage happens - and it hasn't happend anything for the last 6
weeks) -> but as we get paid for this - who cares ))

 

[Ken Wright]

Gissa Job!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I want one of those kind of
jobs!!!!!!!!!!!!!!!!!!! <g>

 

[Frank Kabel]

Gissa Job!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I want one of those kind of
jobs!!!!!!!!!!!!!!!!!!! <g>

Ken
and the best thing is though we told our customer that this kind of
support is too expensive and not longer required he still insisted that
the project team stays. So we all currently improve some of our skills
while earning money -> best thing i've ever had

Frank

 

[Ken Wright]

I agree with him - In fact I think you should increase the team size by at least
1 <g>

 

[Harald Staff]

Gissa Job!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I want one of those kind of
jobs!!!!!!!!!!!!!!!!!!! <g>

Second that. Enjoy every minute of it as long as it last.
And I must say you use the time very wise, Frank, it would be very easy to
fall into laziness and apathy in a situation like that.

Best wishes Harald

 

[Frank Kabel]

Second that. Enjoy every minute of it as long as it last.
And I must say you use the time very wise, Frank, it would be very
easy to fall into laziness and apathy in a situation like that.

Best wishes Harald

O.K. so if I need more poeple in this project I know at least two who
will join ))))

best wishes to you
Frank