I have a worksheet of employees with last names in column B, first names in column C, and concatenate full names in column S. In a separate worksheet I would like to be able to show a count of the number of employees based on various criteria without counting the same employee twice. Some employees are only in the data worksheet once, but other employees are listed multiple times because they hold more than one position. Thanks.
=sumproduct((a1:a100<>"")/countifs(a1:a100,a1:a100&""))
if your data are in a1:a100.
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
Thank you that works. Now I would like to add another criteria. I would like to find only those employees who have an X in column I of the original worksheet and still not get the duplicates. Can you help?
=SUMPRODUCT((A1:A10<>"")*(B1:B10="x")/COUNTIFS(A1:A10,A1:A10&"",B1:B10,B1:B10&""))
Thank you so much. It works great. Can you tell me how each piece of the formula functions to get the correct answer. I know what the ranges refer to but I'm unclear what the &"" does and how it is that the duplicates are actually eliminated in the count. I am was an English major, and I never did very well in algebra so creating formulas is very challenging for me. If I have the written explanation for each step it helps me learn the formulas better. Thanks again!
if you select the cell where the formula has been entered and invoke the Evaluate Formula function in Excel (ALT > M > V), it will help you step through each step of the formula. clicking on Evaluate progresses you through each step the application takes to arrive at the final result.
How come when I put a value in for X such as "*tsd*" the answer comes back as Zero?
=SUMPRODUCT((A1:A10<>"")*(B1:B10="x")/COUNTIFS(A1:A10,A1:A10&"",B1:B10,B1:B10&""))
"wildcards" like * and ? don't work with comparison operators like =
In any case the suggested formula works based on exact matches, so if you want a "wildcard" type search I think you need another approach. This formula will give you the number of different values in A1:A10 where "tsd" appears somewhere in the text in B1:B10
=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("tsd",B1:B10)),IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0))),ROW(A1:A10)-ROW(A1)+1),1))
That's an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER
Audere est facere
Thank you. It works great. I never would have been able to figure this out without help from this forum.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks