Count SUMPRODUCT Match and a second criteria

[sdkorin]

Greeting!

I'm trying to figure out how to count a specific group of people in a workbook.
My workbook is for staffing and name populate out of another workbook with specific character formatting (special characters are attached to the name specifying gender and certifications) Generally peoples names should only appear once on the workbook; however, there is a section that they can be placed twice (due to them working one task part of the day and another the other part of the day). Currently I am using a SUMPRODUCT Match formula to count those that appear in both areas (for example =SUMPRODUCT(--(ISNUMBER(MATCH($B$5:$B$28,$AY$34:$AY$51,0)))) ). So, if "Sayla Mass 2130-0600 ˈ✔KÐ87" appears in both areas, she counts in a specific cell, but if she only appears in one of the areas, she counts in a cell specific for those areas (using a -sum to subtract counts from the both areas cell) However, I would like to break them up by gender as well.
So, what I'd like to do is count only those that appear in both areas by the gender specific special character (males= · and females= ˈ )

Any ideas?

I appreciate everyone's help.

 

[hernantorres23]

Hi, we are pleased to help you, but please, put your data example, using XL2BB.
Note: If would necessary, pay attention to this post XL2BB 2 Square

In some cases, users are disabled for install an add-in. If this condition is applicable, share your file with Dropbox, Drive, etc.

Also, you can copy and paste your data, formatting until if look like your Workbook

 

[sdkorin]

Here's a better example:

E2, Looking for males in range:

=COUNTIF($A$2:$A$6,"*·*")

F2, Looking for males in range:

=COUNTIF($A$2:$A$6,"*ˈ*")

E3 and F3 are the same as E2 and F2 except looking in column B

D5, Looking for duplicates in both column A and B:

=SUMPRODUCT(--(ISNUMBER(MATCH($A$2:$A$6,$B$2:$B$6,0))))

E and F on row 5 are adding the males and females from both columns the get the total staffed. However, it's still including the duplicates for each area.

I am trying to get E4 to count the duplicate males ("*·*") and F4 to count the duplicate females ("*ˈ*"). That way I can subtract them from the total staffed for each gender.

	A	B	C	D	E	F
1	Area 1	Area 2	Areas	Total	Males	Females
2	Angel 1130-2000 ·✔LKÐ7?	Mason 1330-2200 ˈÄ✔KÐ	Staffed A1	4	3	0
3	Orwell 1200-2030 ·✔KÐ?	Shivers 1130-2000 ·Ä✔Ð?	Staffed A2	3	1	1
4	Shivers 1130-2000 ·Ä✔Ð?		Staffed A1/2	1
5			Total Staffed	6	4	1
6

 

[jasonb75]

No sample needed to answer such a simple question @hernantorres23

With the existing formula provided, there is enough information in the post to provide an answer.

@sdkorin please try this revised version of your formula, all you will need to do is replace the word Gender for the correct special character. The special characters that you've used appear to be from a non standard set, so I've done it this way to make it easier to see and to eliminate potential errors.

=SUMPRODUCT(ISNUMBER(MATCH($B$5:$B$28,$AY$34:$AY$51,0))*ISNUMBER(SEARCH("Gender",$B$5:$B$28)))

 

[sdkorin]

Thank you @jasonb75 , seems to be working perfectly at the moment.
It's funny because I tried something almost exactly like this, except I was using both ranges again and not SEARCH. There is still much about excel I don't know, I really appreciate the help.

Just out of curiosity, is there a way to add a second criteria, beside just the gender to this formula? Like, duplicates + gender + another criteria?

 

[sdkorin]

@jasonb75 , I actual figured it out. I didn't think it would be as simple as just adding another *ISNUMBER(SEARCH("criteria",$B$5:$B$28))) after the first SEARCH.

Thanks!

 

[jasonb75]

You're welcome

Here's another way to do the same thing, which you may find a bit easier to set up with more criteria. (based on Staffed A1/2 on your example for easier reference).

Total

=SUM(IFERROR(1/COUNTIFS($A$2:$B$6,$A$2:$B$6),))

Gender

=SUM(IFERROR(1/COUNTIFS($A$2:$B$6,$A$2:$B$6,$A$2:$B$6,"*·*"),))

Additional criteria

=SUM(IFERROR(1/COUNTIFS($A$2:$B$6,$A$2:$B$6,$A$2:$B$6,"*·*",$A$2:$B$6,"criteria"),))