I need some urgent help. I need to find the number of unique agencies below for number 1, but I need to exclude the agency named "HUMA" from the results. In this case, the answer would be 1(ie. Agency SOAP). What's the formula to do so? Assume the column "Number" is column A and column "Agency" is column B.
Number Agency
1 SOAP
2 BALL
1 HUMA
2 NIJA
2 POIS
1 SOAP
Thank you!
One could use two COUNTIF formulas, with one COUNTIF formula subtracting the result for the item you need to exclude.
Welcome to the forum.
Either of these formulae should do what you want:
Formula:
=SUM(IF(FREQUENCY(IF((A2:A7=1)*(B2:B7<>"HUMA"),IF(B2:B7<>"",MATCH(B2:B7,B2:B7,0))),ROW(B2:B7)-ROW(B2)+1),1))
=SUM(--(FREQUENCY(IF((A2:A7=1)*(B2:B7<>"HUMA"), COUNTIF(B2:B7, "<"&B2:B7), ""), COUNTIF(B2:B7, "<"&B2:B7))>0))
Important: these are array formulae so need to be entered using Ctrl-Shift-Enter not just Enter. You will know the array is active when you see curly brackets { } appear around your formula - don't try to enter them yourself. If you do not Ctrl-Shift-Enter you will get an error or a clearly incorrect result. If that happens, just click into the formula bar and try again.
Change the ranges to match your actual data, of course
Hope that helps.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks