LinkBack URL
About LinkBacks
Hi
I am looking for a formula that does a multiple criteria.
I am attaching a spreadsheet where i want to count based on heading 1 and P1 avg.
If some one can please help it be highly appreciated.
Hi
I am looking for a formula that does a multiple criteria.
I am attaching a spreadsheet where i want to count based on heading 1 and P1 avg.
If some one can please help it be highly appreciated.
Good afternoon allana13
It is not entirely obvious from your post or your file exactly what you want to count.
Would you also please alter your profile, as this still says you are using Excel 2003, but the file you have posted is an Excel 2007 (or later) format.
This matters, because Excel 2007 introduced some new functions that users have been crying out for for years. You need to use one of these new functions, =COUNTIFS().
HTH
DominicB
Hi Dominic
Thanks for the reply
i will alter the Version of excel on my profile.
Basically what i am trying to achieve is the following.
Currently in cell J12 i have a simple countif formula that is counting the number of times "009" is present in column C
What i want is the number of times column E is populated with values using the criteria "009". So its basically a count if looking at column C as a criteria and Column E as a criteria.
This is because a cell that has "009" may not have a value in column E, therefore it shouldn't be counted in the total in cell J12
I hope i am being clear.....
Thanks for the help Folks.....
Hi allana,
You are having result as 9 as per your formula "=COUNTIF(C13:C964,I12)" .. if this is now what you want, then share the results manually which you want to achieve using formulas... thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
Hi Dilip thanks for your reply....
For example if you look at "010" in colum c it appears 18 times if you count it from cell c22 to cell c39, however if you you count from cell f22 to cell f39 you will get 14 as the result this is because 4 cells do not have a value....
It is that 14 i would like to show in cell K13 which is why i need a multiple countif formula that will look at column c and use the crieteria in column I and put the counted value of column F in column K and the counted value of column g in column L
Hope this explains it... many thanks
Striving for perfection....
Hi
Please let me know if what i have asked makes sense?
Many thanks
Hi
Do you mean in J13 (for P1 Count)and copy down this?
=SUMPRODUCT(($C$13:$C$2000=I12)*($E$13:$E$2000<>""))
If YES, same for the other 2 Counts.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Try
=COUNTIFS($C$13:$C$964,I12,$F$13:$F$964,"<>"&"") in K12
Last edited by Cutter; 05-24-2012 at 11:26 AM. Reason: Specified formula location
Actually this in J12 (copied over, then down)
=COUNTIFS($C$13:$C$964,$I12,E$13:E$964,"<>"&"")
Is this what you are looking for?
In J12:
=COUNTIFS($C$13:$C$964,$I12,E$13:E$964,"<>")
copied across and down the columns
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Dear NBVC & Cutter many thanks for your reply much appreciated, however when i use your formula it gives me #Name?
Dear Fotis using your formula it has given me the desired result. I sincerely thank you very much for your help on this.
I am very pleased to see folks helping each other out on this forum.... thanks once again one and all.....
Our COUNTIFS formula will give you that error if you are using EXCEL 2003 or earlier. Your profile indicates 2010 and your file is a .xlsx, so it should work.
My Apologies mate....
My home laptop has 2003 installed on it which is where i was trying it. I will try it on the other pc that has 2010....
Thanks for pointing it out....
Many thanks
Thanks Folks
Both Formulas work. Much appreciate all your inputs.
Regards
Allana
Another step closer to perfection then?
Don't forget to mark your thread as SOLVED (for instructions click on Forum Rules @ top of page and see rule #9).
And thanks for the 'star tap' (just saw it).
Last edited by Cutter; 06-14-2012 at 09:34 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks