COUNTIF formula across multiple ranges
Solved
Mowkey
Posted messages
18
Status
Membre
-
Mowkey Posted messages 18 Status Membre -
Mowkey Posted messages 18 Status Membre -
Hello,
After extensive research, I don't feel like I've found a solution to my problem. Here it is:
I would like to use the COUNTIF function to count how many times the name of an agent appears, but across multiple ranges from different sheets.
Example: how many times does the name of an agent, for example DUPONT, appear between the range A5 and M5 of 'Sheet1', as well as between the range A5 and M5 of 'Sheet2' ... And this across 12 different sheets!
The downside is that I have a long list of about 100 agents for whom I need to develop my formula.
I did find a solution using a SUMPRODUCT formula like this:
=SUMPRODUCT((January!E5:January!AO5="DUPONT")+(February!E5:February!AO5="DUPONT")+(March!E5:March!AO5="DUPONT")+(April!E5:April!AO5="DUPONT")+(May!E5:May!AO5="DUPONT")+(June!E5:June!AO5="DUPONT")+(July!E5:July!AO5="DUPONT")+(August!E5:August!AO5="DUPONT")+(September!E5:September!AO5="DUPONT")+(October!E5:October!AO5="DUPONT")+(November!E5:November!AO5="DUPONT")+(December!E5:December!AO5="DUPONT"))
The downside is that I can't expand this formula to my list of agents, as I would need to change the agent's name for each one... On a list of 100 people, that's quite tedious.
And knowing that putting the name of the cell (for example, instead of "DUPONT", putting "Sheet13!C4" (the cell where the name DUPONT is located)) doesn't work.
I hope I have been clear enough =)
Thank you in advance, don't hesitate to reach out if you need further clarification.
Best regards,
Mowkey
Configuration: Windows XP / Firefox 10.0.2
After extensive research, I don't feel like I've found a solution to my problem. Here it is:
I would like to use the COUNTIF function to count how many times the name of an agent appears, but across multiple ranges from different sheets.
Example: how many times does the name of an agent, for example DUPONT, appear between the range A5 and M5 of 'Sheet1', as well as between the range A5 and M5 of 'Sheet2' ... And this across 12 different sheets!
The downside is that I have a long list of about 100 agents for whom I need to develop my formula.
I did find a solution using a SUMPRODUCT formula like this:
=SUMPRODUCT((January!E5:January!AO5="DUPONT")+(February!E5:February!AO5="DUPONT")+(March!E5:March!AO5="DUPONT")+(April!E5:April!AO5="DUPONT")+(May!E5:May!AO5="DUPONT")+(June!E5:June!AO5="DUPONT")+(July!E5:July!AO5="DUPONT")+(August!E5:August!AO5="DUPONT")+(September!E5:September!AO5="DUPONT")+(October!E5:October!AO5="DUPONT")+(November!E5:November!AO5="DUPONT")+(December!E5:December!AO5="DUPONT"))
The downside is that I can't expand this formula to my list of agents, as I would need to change the agent's name for each one... On a list of 100 people, that's quite tedious.
And knowing that putting the name of the cell (for example, instead of "DUPONT", putting "Sheet13!C4" (the cell where the name DUPONT is located)) doesn't work.
I hope I have been clear enough =)
Thank you in advance, don't hesitate to reach out if you need further clarification.
Best regards,
Mowkey
Configuration: Windows XP / Firefox 10.0.2
3 réponses
Hello
on sheet 13
=COUNTIF(January!E5:AO5,C4)+COUNTIF(February!E5:AO5,C4)+ etc...
should work if the names of the agents are written in the same way in C4 sheet 13 and the lines A5:M5
(SUMPRODUCT((....))also works, but it is not necessary to repeat the sheet name on each cell range _ Sheet1!A5:M5 for example is perfectly fine
best regards
To err is human, to persist is diabolical.
on sheet 13
=COUNTIF(January!E5:AO5,C4)+COUNTIF(February!E5:AO5,C4)+ etc...
should work if the names of the agents are written in the same way in C4 sheet 13 and the lines A5:M5
(SUMPRODUCT((....))also works, but it is not necessary to repeat the sheet name on each cell range _ Sheet1!A5:M5 for example is perfectly fine
best regards
To err is human, to persist is diabolical.
Hello
With SUMPRODUCT
=SUMPRODUCT((January!$E$5:$AO$5=A11)+(February!$E$5:$AO$5=A11)+(March!$E$5:$AO$5=A11)+(April!$E$5:$AO$5=A11)+(May!$E$5:$AO$5=A11)) etc...
In column A put the list of names and in B this formula (to be extended until December)
--
Practice makes perfect. - You only see the mason when you're up against the wall - one always learns from their mistakes.
With SUMPRODUCT
=SUMPRODUCT((January!$E$5:$AO$5=A11)+(February!$E$5:$AO$5=A11)+(March!$E$5:$AO$5=A11)+(April!$E$5:$AO$5=A11)+(May!$E$5:$AO$5=A11)) etc...
In column A put the list of names and in B this formula (to be extended until December)
--
Practice makes perfect. - You only see the mason when you're up against the wall - one always learns from their mistakes.
Hello,
=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"}&"'!A1:D3");"DUPONT"))
Another possibility is to enter the names of your sheets in a range, for example if you have 12 sheets, in P1 January, in P2 February, etc... no empty cells in this range
=SUMPRODUCT(COUNTIF(INDIRECT("'"&P1:P12&"'!E5:AO5");"DUPONT"))
by naming the range where the sheets are entered, for example range P1:P12 named Sheet and the criterion entered in Q1
=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheet&"'!A1:D3");Q1))
Cheers
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"}&"'!A1:D3");"DUPONT"))
Another possibility is to enter the names of your sheets in a range, for example if you have 12 sheets, in P1 January, in P2 February, etc... no empty cells in this range
=SUMPRODUCT(COUNTIF(INDIRECT("'"&P1:P12&"'!E5:AO5");"DUPONT"))
by naming the range where the sheets are entered, for example range P1:P12 named Sheet and the criterion entered in Q1
=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheet&"'!A1:D3");Q1))
Cheers
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
I didn't know that we could combine formulas with the "+".
It's perfect, thank you for your responsiveness!
Have a great day!
Mowkey