Count with multiple conditions

ab1275

New Member
Joined
Jul 19, 2016
Messages
31
Office Version
  1. 365
Platform
  1. Windows
[TABLE="width: 500"]
<tbody>[TR]
[TD]Item#[/TD]
[TD]Sending from[/TD]
[TD]Receiving at[/TD]
[TD]Received by[/TD]
[TD]# of receivers[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]warehouse1[/TD]
[TD]HQ[/TD]
[TD]Rep1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]
warehouse1
<strike></strike>
[/TD]
[TD]<strike></strike>
HQ
<strike></strike>
[/TD]
[TD]Rep2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]
warehouse1
<strike></strike>
[/TD]
[TD]<strike></strike>
HQ
<strike></strike>
[/TD]
[TD]Rep3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]
warehouse2
[/TD]
[TD]warehouse3[/TD]
[TD]
Rep4
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]
warehouse2
<strike></strike>
[/TD]
[TD]
warehouse3
[/TD]
[TD]Rep5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]852[/TD]
[TD]
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>
[/TD]
[TD]<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>
[/TD]
[TD]<strike></strike>
Rep1
<strike></strike>
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]852[/TD]
[TD]
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>
[/TD]
[TD]<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>
[/TD]
[TD]<strike></strike>
Rep1
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[/TR]
[TR]
[TD]852[/TD]
[TD]
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>
[/TD]
[TD]<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>
[/TD]
[TD]<strike></strike>
Rep3
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[/TR]
[TR]
[TD]852[/TD]
[TD]
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>
[/TD]
[TD]<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>
[/TD]
[TD]<strike></strike>
Rep3
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]

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?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.
Excel Workbook
ABCDE
1Item#Sending fromReceiving atReceived by# of receivers
2123warehouse1HQRep13
3123warehouse1HQRep2
4123warehouse1HQRep3
5456warehouse2warehouse3Rep42
6456warehouse2warehouse3Rep5
7852warehouse1warehouse3Rep12
8852warehouse1warehouse3Rep1
9852warehouse1warehouse3Rep3
10852warehouse1warehouse3Rep3
Sheet
 
Upvote 0
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?
 
Upvote 0
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.


<colgroup><col width="513"></colgroup>
[TD="width: 513"]MATCH($A$2:$A$10&"/"&$D$2:$D$10,$A$2:$A$10&"/"&$D$2:$D$10,0)
returns:
[TABLE="width: 513"]
<colgroup><col width="513"></colgroup>[TR]
[TD="width: 513"]{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.


<colgroup><col width="636"></colgroup>
[TD="width: 636"]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

<colgroup><col width="636"></colgroup>
[TD="width: 636"]{FALSE;FALSE;FALSE;FALSE;FALSE;6;6;8;8}

Add the Frequency function and we get:
[TABLE="width: 943"]
<colgroup><col width="943"></colgroup>[TR]
[TD="width: 943"]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

<colgroup><col width="943"></colgroup>
[TD="width: 943"]{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.
[TABLE="width: 968"]
<colgroup><col width="968"></colgroup>[TR]
[TD="width: 968"]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:

<colgroup><col width="968"></colgroup>
[TD="width: 968"]{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
ABCDE
1Item#Sending fromReceiving atReceived by# of receivers
2123warehouse1HQRep13
3123warehouse1HQRep2
4123warehouse1HQRep3
5456warehouse2warehouse3Rep42
6456warehouse2warehouse3Rep5
7852warehouse1warehouse3Rep12
8852warehouse1warehouse3Rep1
9852warehouse1warehouse3Rep3
10852warehouse1warehouse3Rep3
Sheet



[/TD]
[/TD]
[/TD]
[/TD]
[/TD]

<colgroup><col width="636"></colgroup>
[TD="width: 636"][/TD]
[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[/TABLE]
 
Upvote 0
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)))
 
Upvote 0
This
IF(COUNTIF($B$2:$B79702,B2)>1
should be
IF(COUNTIF($B$2:$B2,B2)>1
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top