SumIf and CountIf Multiple Criteria

[Rose]

Help Please! I need to learn how to get the average of two columns excluding
certain types which are in a third column. Column U is Survey Score column V
is Additional Survey Score but I need to exclude the type of "PTA" survey
scores which are in column B.

The current formula we use just to get the average is of the two columns is:
=SUMIF(U11:V1143,">=0")/MAX(1,COUNTIF(U11:V1143,">=0"))

What do I need to add to the formula to exclude the "PTA" scores, the score
types are in column B. Or do I need to use a totally different formula to
achieve this?

Best Regards,
Rose

 

[Max]

Try:
=SUMPRODUCT((B11:B1143<>"")*(B11:B1143<>"PTA")*(U11:U1143>=0)*(V11:V1143>=0)*U11:V1143)/MAX(1,SUMPRODUCT((B11:B1143<>"")*(B11:B1143<>"PTA")*(U11:U1143>=0)*(V11:V1143>=0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:54
xdemechanik

 

[Rose]

Thanks so much for your quick reply. I copied and pasted to my worksheet but
got the dreaded #VALUE!

Any suggestions, I really appreciate your help.

Many thanks,
Rose

 

[Peo Sjoblom]

One possible way

=SUMPRODUCT((B11:B1143<>"PTA")*(U11:V1143>=0)*(U11:V1143<>"")*(U11:V1143))/MAX(1,SUMPRODUCT((B11:B1143<>"PTA")*(U11:V1143>=0)*(U11:V1143<>"")))


--


Regards,


Peo Sjoblom

 

[Rose]

Hi Peo,
Thanks for your quick response, I copied and pasted your suggestion into my
worksheet but got the dreaded #VALUE! error.

Any other suggestions please?

Many thanks for your time,
Rose

 

[Peo Sjoblom]

The reason you get a value error is that you have text in U11:V1143, you
should always avoid that as much as possible. Btw, why are you using >=0 in
your original formula, can there be negative numbers?


--


Regards,


Peo Sjoblom

 

[Rose]

Thank you so much, you saved my life - this is for work. There was text in
the columns in particular the word "blank" the worksheet is a pivot table and
sometimes when I refresh the data the pivot table will insert the word blank.
I deselected blank from those columns and it worked. As you can probably
already tell I am self taught and a beginner in Excel.

To answer your question about why are you using >=0 in your original
formula, can there be negative numbers? I'm not sure as this came from my
boss, we were having trouble getting the average formula to work in the first
place because the columns had empty cells so she found this formula somewhere
and had me enter it.

Now that we excluded the PTA survey's from that cell she wants only the PTA
survey average in the cell next to it . What would that formula look like
please?

Again many thanks for your help!
Rose

 

[Peo Sjoblom]

If Max's formula worked then just change this part in all occurrences

B11:B1143<>"PTA"

to this

B11:B1143="PTA"



--


Regards,


Peo Sjoblom

 

[Rose]

I spoke too soon I forgot that by deselecting the "blanks" from the pivot
table it takes all the data out that had the blanks so my numbers are wrong.

Is there a way to fix your formula to include the cells that have the word
blank or text in it?

Thanks so much for your help,
Rose