Countif with multiple conditions
In my Sheet "List" I have list of persons working on different projects.
I prepared graph after putting conditions on Project Type, Project Size, Project Year & Position (PM Project Manger)
Every thing was done a in a nice manner with the help of below formula.
PJ TYPE , PJSIZE, PJYEAR, POSITION are ranges names.
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"))
But the problem was occured that in a year if a person work on small project more than once then he will be counted only once. But if he has worked in same year on Medium or Larage project then they will be counted separately. I tried to oversome the problem with the help of Pivot Table and put manually some legend P1, P2 & P3 against the person name if he is working on same type of project in same year. then count only P1 in my formula to count how many Project Manager worked on Project. like
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"),--(PMCOUNT="P1"))
Now i am trying that in a separate columm of # of PM there must be a formula which only put P or 1 for a person if he is working on same project in a year but i want that p or 1 only appear against his first entry i duplicate. for other persons it automatically enter 1 or p if they are appearing only once. i have tried a lot while using countif with multiple conditions but all in vain.
I am writing all this in a great hurry as i have to leave office for home. I am attaching the file as well for your review and any advise.
Thanks in advance
Last edited by mubashir aziz; 04-16-2009 at 05:33 AM. Reason: Problem has solved...
I don't install Excel 2007, so I use Excel XP to test it.
I add two names ENAME, PF1 to solve it by using array formula.
The sample solution is written in the C6:C8 of the Graphs for the attachment.
Other cells you can do the same method.
Remember to press Ctrl+Shift+Enter after input array formula.
Thanks a lot WK, I don't believe that how amazingly you solve the problem else i was getting hopeless .... this is your first post and i think you just came like an angel to solve my problem ..... again thank you very much............. I solve the 2nd part of multi discipline by creating PF2 .......
If you have time than please clarify this formula as i got the idea of PF1 , PF2 & ENANE but
={SUM(PF2*(MATCH(IF(PF2,ENAME),IF(PF2,ENAME),0)=(ROW(ENAME))-2))}
Can you define me exactly how it works ...... especially Match & Row(ename)-2 .....
Last edited by mubashir aziz; 04-15-2009 at 03:08 AM.
I use a simple sample to explain it as attached.
At first, define some symbol as follows
// Get column A values corresponded to column B = "S"
S1 = IF(B1:B5="S",A1:A5)
S1 = {FALSE;FALSE;"C";"B";"B"}
// Find the appear position
S2 = MATCH(IF(B1:B5="S",A1:A5),IF(B1:B5="S",A1:A5),0)
S2 = {1;1;3;4;4}
// Check whether the first appear position
S3 = (MATCH(IF(B1:B5="S",A1:A5),IF(B1:B5="S",A1:A5),0)=ROW(A1:A5))
{TRUE;FALSE;TRUE;TRUE;FALSE}
S4 =((B1:B5)="S")
{FALSE;FALSE;TRUE;TRUE;TRUE}
// Filter error
S3*S4 = {0;0;1;1;0}
//
Sum(S3*S4) = 2
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks