Count with multiple conditions

[ab1275]

Item#	Sending from	Receiving at	Received by	# of receivers
123	warehouse1	HQ	Rep1	3
123	warehouse1​ <strike></strike>	<strike></strike> HQ​ <strike></strike>	Rep2
123	warehouse1​ <strike></strike>	<strike></strike> HQ​ <strike></strike>	Rep3
456	warehouse2​	warehouse3	Rep4​	2
456	warehouse2​ <strike></strike>	warehouse3​	Rep5
852	warehouse1​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	<strike></strike> warehouse3​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	<strike></strike> Rep1​ <strike></strike>	2
852	warehouse1​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	<strike></strike> warehouse3​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	<strike></strike> Rep1​ <strike></strike>	<strike></strike>
852	warehouse1​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	<strike></strike> warehouse3​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	<strike></strike> Rep3​ <strike></strike>	<strike></strike>
852	warehouse1​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	<strike></strike> warehouse3​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>	<strike></strike> Rep3​ <strike></strike>	<strike></strike>

<tbody>
</tbody>

Above is a mockup data that I have. I am looking to populate column "# of receivers" with the number of unique representatives for each item. Is this possible with a formula in the "# of receivers" column?

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B	C	D	E
1	Item#	Sending from	Receiving at	Received by	# of receivers
2	123	warehouse1	HQ	Rep1	3
3	123	warehouse1	HQ	Rep2
4	123	warehouse1	HQ	Rep3
5	456	warehouse2	warehouse3	Rep4	2
6	456	warehouse2	warehouse3	Rep5
7	852	warehouse1	warehouse3	Rep1	2
8	852	warehouse1	warehouse3	Rep1
9	852	warehouse1	warehouse3	Rep3
10	852	warehouse1	warehouse3	Rep3
Sheet

 

[ab1275]

Thank you so so much! It worked...My file will have new rows every month...will I have to change the row count everytime I add rows of data?
I might be asking too much of you...but wondering how can I learn what each part of the formula does and how it all comes together?

 

[AhoyNC]

Depends on how you will enter the new rows. If you enter them manually or with copy / paste and your data would never go below say row 5000 you could use that as the ending row in the formula and just drag the formula down for the new rows each month. Another way would be to use a dynamic range (see link below on how to do this).
https://www.youtube.com/watch?v=13tY16Y19TY

Here is a break down of the formula. Based on the formula in E7 for item#852 which had duplicates.

The MATCH function will return the first row number of where it finds a match.

MATCH($A$2:$A$10&"/"&$D$2:$D$10,$A$2:$A$10&"/"&$D$2:$D$10,0) returns: {1;2;3;4;5;6;6;8;8} Notice it returns row 6 and row 8 twice for the duplicates. When we add the IF function to return only item#852. IF($A$2:$A$10=A7,MATCH($A$2:$A$10&"/"&$D$2:$D$10,$A$2:$A$10&"/"&$D$2:$D$10,0)) Returns {FALSE;FALSE;FALSE;FALSE;FALSE;6;6;8;8} Add the Frequency function and we get: FREQUENCY(IF($A$2:$A$10=A7,MATCH($A$2:$A$10&"/"&$D$2:$D$10,$A$2:$A$10&"/"&$D$2:$D$10,0)),ROW($A$2:$A$10)-ROW($A$2)+1) Returns {0;0;0;0;0;2;0;2;0;0} The ROW function is just used to give us the Bin range for the Frequency. It returns the numbers 1 - 9 in this example. Since in Excel 0 = FALSE and any number greater than 0 = TRUE. When we add the next IF function we get. IF(FREQUENCY(IF($A$2:$A$10=A7,MATCH($A$2:$A$10&"/"&$D$2:$D$10,$A$2:$A$10&"/"&$D$2:$D$10,0)),ROW($A$2:$A$10)-ROW($A$2)+1),1)) Returns: {FALSE;FALSE;FALSE;FALSE;FALSE;1;FALSE;1;FALSE;FALSE} We then SUM the above array and get our answer of 2. The COUNT function is just used to return a blank anytime the count of the item# is greater than 1. Hope that helps. Excel Workbook A B C D E 1 Item# Sending from Receiving at Received by # of receivers 2 123 warehouse1 HQ Rep1 3 3 123 warehouse1 HQ Rep2 4 123 warehouse1 HQ Rep3 5 456 warehouse2 warehouse3 Rep4 2 6 456 warehouse2 warehouse3 Rep5 7 852 warehouse1 warehouse3 Rep1 2 8 852 warehouse1 warehouse3 Rep1 9 852 warehouse1 warehouse3 Rep3 10 852 warehouse1 warehouse3 Rep3 Sheet <colgroup><col width="968"></colgroup> <colgroup><col width="968"></colgroup> <colgroup><col width="943"></colgroup> <colgroup><col width="943"></colgroup> <colgroup><col width="636"></colgroup> <colgroup><col width="636"></colgroup> <colgroup><col width="636"></colgroup> <colgroup><col width="513"></colgroup>

<colgroup><col width="513"></colgroup>

 

[ab1275]

Wow...Thank you so much for the explanation.

Below is the formula I entered (column B is equivalent to A in the example. Z to D ) and for some reason it now shows all blanks. I noticed that B2 seemed to stay as is in all the cells. Maybe I am copying/entering the array wrong?

=IF(COUNTIF($B$2:$B79702,B2)>1,"",SUM(IF(FREQUENCY(IF($B$2:$B$79702=B2,MATCH($B$2:$B$79702&"/"&$Z$2:$Z$79702,$B$2:$B$79702&"/"&$Z$2:$Z$79702,0)),ROW($B$2:$B$79702)-ROW($B$2)+1),1)))

 

[Fluff]

This
IF(COUNTIF($B$2:$B79702,B2)>1
should be
IF(COUNTIF($B$2:$B2,B2)>1