Count If with multiple criteria and unique values

D

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
Hi,
I dont know if this is possible or not but thought I'd ask anyway.

I have a sheet of data and want to run a count on the values in Column A with the following criteria:

- If the value in column B is repeated then the value in column A can only be counted once...
- Column C must equal 'M'.


Example:
A.....B.....C
1.....42....M
1.....42....M
2.....41....A
2.....41....M
1.....56....M


In this example 1 would be counted 2 times, and 2 would be counted once.

I have about 15 sheets of data like this and would like a master sheet to look into each sheet and give me a count of each so id prefer to not use the advanced filter => unique variables option if possible.

Any help would be greatly appreciated.

Dixon.
 
Sort by date Sort by votes
RIZVI said:
Hi,

I have a similar situation and need your help, please,

1.) In table Y6 to AJ18, the customers are listed in Column A. There are repeat names at times.

2.) Column Z has two locations from where the customer buys.

3) Column AD has the amount.

I need in column AS, the names of the customer as unique entries. That is the name shall not repeat here, unless the same customer has purchased from 2 different locations.
In Column AT I want sum total of the purchases made by them depending upon the unit they purchased from.

Thanks in anticipation.


RGDS,

Rizvi
Click to expand...

Care to post a small sample (of only the relevant ranges) along with the outcome you want to see?
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
[TABLE="width: 455"]
<colgroup><col width="122" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4461;"> <col width="45" style="width: 34pt; mso-width-source: userset; mso-width-alt: 1645;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;" span="3"> <tbody>[TR]
[TD="width: 122, bgcolor: #00B050"]Customer
[/TD]
[TD="width: 45, bgcolor: #00B050"]Com.[/TD]
[TD="width: 73, bgcolor: #00B050"]Inv Amount[/TD]
[TD="width: 57, bgcolor: #00B050"]Over Due Days[/TD]
[TD="width: 102, bgcolor: #00B050"]Customer[/TD]
[TD="width: 102, bgcolor: #00B050"]Com.[/TD]
[TD="width: 102, bgcolor: #00B050"]Total Amount[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]Filpak[/TD]
[TD="bgcolor: #92D050"]DCM[/TD]
[TD="bgcolor: #92D050, align: right"]₹ 11,450[/TD]
[TD="bgcolor: #92D050"]13[/TD]
[TD="bgcolor: #92D050"]Filpack[/TD]
[TD="bgcolor: #92D050"]DCM[/TD]
[TD="bgcolor: #92D050"]11450
[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]Huntsman[/TD]
[TD="bgcolor: #92D050"]DCM[/TD]
[TD="bgcolor: #92D050, align: right"]₹ 18,210[/TD]
[TD="bgcolor: #92D050"]62[/TD]
[TD="bgcolor: #92D050"]Huntsman[/TD]
[TD="bgcolor: #92D050"]DCM[/TD]
[TD="bgcolor: #92D050"]59610
[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]Huntsman[/TD]
[TD="bgcolor: #92D050"]DCM[/TD]
[TD="bgcolor: #92D050, align: right"]₹ 6,000[/TD]
[TD="bgcolor: #92D050"]84[/TD]
[TD="bgcolor: #92D050"]Jetinks[/TD]
[TD="bgcolor: #92D050"]DCM[/TD]
[TD="bgcolor: #92D050"]12000[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]Jetinks
[/TD]
[TD="bgcolor: #92D050"]DCM[/TD]
[TD="bgcolor: #92D050, align: right"]₹ 12,000[/TD]
[TD="bgcolor: #92D050"]94[/TD]
[TD="bgcolor: #92D050"]Real Drinks[/TD]
[TD="bgcolor: #92D050"]DCM[/TD]
[TD="bgcolor: #92D050"]12000[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]Huntsman[/TD]
[TD="bgcolor: #92D050"]DCM[/TD]
[TD="bgcolor: #92D050, align: right"]₹ 23,400[/TD]
[TD="bgcolor: #92D050"]15[/TD]
[TD="bgcolor: #92D050"]Filpack[/TD]
[TD="bgcolor: #92D050"]TGP[/TD]
[TD="bgcolor: #92D050"]23450[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]Real Drinks[/TD]
[TD="bgcolor: #92D050"]TGP[/TD]
[TD="bgcolor: #92D050, align: right"]₹ 23,450[/TD]
[TD="bgcolor: #92D050"]32[/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]Huntsman[/TD]
[TD="bgcolor: #92D050"]DCM[/TD]
[TD="bgcolor: #92D050, align: right"]₹ 12,000[/TD]
[TD="bgcolor: #92D050"]62[/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]Filpak[/TD]
[TD="bgcolor: #92D050"]TGP[/TD]
[TD="bgcolor: #92D050, align: right"]₹ 23,450[/TD]
[TD="bgcolor: #92D050"]14[/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]
[/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[/TR]
</tbody>[/TABLE]


The last 3 columns are the desired result wanted.

RGDS,

Rizvi
 
Upvote 0
Filpak is same customer but has purchased from two different locations, One being DCM and other TGP. So as the collection responsibility will be of different locations, so they have to appear as two different entities in the last three column as shown.

In the table above the last three columns have been filled by me manually to show what results I want excel to give automaticlly.
 
Upvote 0
RIZVI said:
Filpak is same customer but has purchased from two different locations, One being DCM and other TGP. So as the collection responsibility will be of different locations, so they have to appear as two different entities in the last three column as shown.
Click to expand...

OK.

In the table above the last three columns have been filled by me manually to show what results I want excel to give automaticlly.
Click to expand...

I had to check for correctness...

[TABLE="width: 548"]
<TBODY>[TR]
[TD="class: xl63, width: 112, bgcolor: #00b050"]Customer
[/TD]
[TD="class: xl63, width: 64, bgcolor: #00b050"]Com.
[/TD]
[TD="class: xl63, width: 116, bgcolor: #00b050"]Inv Amount
[/TD]
[TD="class: xl63, width: 118, bgcolor: #00b050"]Over Due Days
[/TD]
[TD="class: xl63, width: 138, bgcolor: #00b050"]Customer
[/TD]
[TD="class: xl63, width: 64, bgcolor: #00b050"]Com.
[/TD]
[TD="class: xl63, width: 118, bgcolor: #00b050"]Total Amount
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 112, bgcolor: #92d050"]Filpak
[/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"]DCM
[/TD]
[TD="class: xl66, width: 116, bgcolor: #92d050"]11,450
[/TD]
[TD="class: xl64, width: 118, bgcolor: #92d050"]13
[/TD]
[TD="class: xl64, width: 138, bgcolor: #92d050"]Filpak
[/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"]DCM
[/TD]
[TD="class: xl66, width: 118, bgcolor: #92d050"]11,450
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 112, bgcolor: #92d050"]Huntsman
[/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"]DCM
[/TD]
[TD="class: xl66, width: 116, bgcolor: #92d050"]18,210
[/TD]
[TD="class: xl64, width: 118, bgcolor: #92d050"]62
[/TD]
[TD="class: xl64, width: 138, bgcolor: #92d050"]Huntsman
[/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"]DCM
[/TD]
[TD="class: xl66, width: 118, bgcolor: #92d050"]59,610
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 112, bgcolor: #92d050"]Huntsman
[/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"]DCM
[/TD]
[TD="class: xl66, width: 116, bgcolor: #92d050"]6,000
[/TD]
[TD="class: xl64, width: 118, bgcolor: #92d050"]84
[/TD]
[TD="class: xl64, width: 138, bgcolor: #92d050"]Jetinks
[/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"]DCM
[/TD]
[TD="class: xl66, width: 118, bgcolor: #92d050"]12,000
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 112, bgcolor: #92d050"]Jetinks
[/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"]DCM
[/TD]
[TD="class: xl66, width: 116, bgcolor: #92d050"]12,000
[/TD]
[TD="class: xl64, width: 118, bgcolor: #92d050"]94
[/TD]
[TD="class: xl64, width: 138, bgcolor: #92d050"]Real Drinks
[/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"]TGP
[/TD]
[TD="class: xl66, width: 118, bgcolor: #92d050"]23,450
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 112, bgcolor: #92d050"]Huntsman
[/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"]DCM
[/TD]
[TD="class: xl66, width: 116, bgcolor: #92d050"]23,400
[/TD]
[TD="class: xl64, width: 118, bgcolor: #92d050"]15
[/TD]
[TD="class: xl64, width: 138, bgcolor: #92d050"]Filpak
[/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"]TGP
[/TD]
[TD="class: xl66, width: 118, bgcolor: #92d050"]23,450
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 112, bgcolor: #92d050"]Real Drinks
[/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"]TGP
[/TD]
[TD="class: xl66, width: 116, bgcolor: #92d050"]23,450
[/TD]
[TD="class: xl64, width: 118, bgcolor: #92d050"]32
[/TD]
[TD="class: xl64, width: 138, bgcolor: #92d050"][/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"][/TD]
[TD="class: xl66, width: 118, bgcolor: #92d050"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 112, bgcolor: #92d050"]Huntsman
[/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"]DCM
[/TD]
[TD="class: xl66, width: 116, bgcolor: #92d050"]12,000
[/TD]
[TD="class: xl64, width: 118, bgcolor: #92d050"]62
[/TD]
[TD="class: xl65, width: 138, bgcolor: #92d050"][/TD]
[TD="class: xl65, width: 64, bgcolor: #92d050"][/TD]
[TD="class: xl66, width: 118, bgcolor: #92d050"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 112, bgcolor: #92d050"]Filpak
[/TD]
[TD="class: xl64, width: 64, bgcolor: #92d050"]TGP
[/TD]
[TD="class: xl66, width: 116, bgcolor: #92d050"]23,450
[/TD]
[TD="class: xl64, width: 118, bgcolor: #92d050"]14
[/TD]
[TD="class: xl65, width: 138, bgcolor: #92d050"][/TD]
[TD="class: xl65, width: 64, bgcolor: #92d050"][/TD]
[TD="class: xl65, width: 118, bgcolor: #92d050"][/TD]
[/TR]
</TBODY>[/TABLE]

Define the following using Formulas | Name Manager:

Customer as:
Rich (BB code): 
 =Sheet1!$A$2:$A$9
Com as:
Rich (BB code): 
=Sheet1!$B$2:$B$9
Amount as:
Rich (BB code): 
=Sheet1!&C$2:$C$9
Ivec as:
Rich (BB code): 
=ROW(Customer)-ROW(INDEX(Customer,1,1))+1

E2, control+shift+enter (CSE), not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX(Customer,SMALL(IF(FREQUENCY(IF(Customer<>"",
  MATCH(Customer&"|"&Com,Customer&"|"&Com,0)),Ivec),Ivec),
  ROWS(E$2:E2))),"")

F2, CSE and copy down:
Rich (BB code): 
=IFERROR(INDEX(Com,SMALL(IF(FREQUENCY(IF(Customer<>"",
  MATCH(Customer&"|"&Com,Customer&"|"&Com,0)),Ivec),Ivec),
  ROWS(F$2:F2))),"")

G2, just enter and copy down:
Rich (BB code): 
=IF($E2="","",SUMIFS(Amount,Customer,$E2,Com,$F2))
 
Upvote 0
Dear Aladin,

Thanks for your inputs. But I am too naïve and will need your guidance as I do not know how to define. Please guide me step by step on this and Ivec as to how to do it.

RGDS,

Rizvi
 
Upvote 0
RIZVI said:
Dear Aladin,

Thanks for your inputs. But I am too naïve and will need your guidance as I do not know how to define. Please guide me step by step on this and Ivec as to how to do it.

RGDS,

Rizvi
Click to expand...

Activate Formulas | Name Manager from the ribbon.
Activate New.
Enter Customer in the box for Name.
Enter the definition

=Sheet1!$A$2:$A$9

in the Refers to box.
Click OK

Repeat the process for each name. Note that Sheet1 is assumed the sheet housing the data. Adjust to suit.
 
Upvote 0
I have been able to define Customer, Com & Amount.

But not able to figure out how and where to define Ivec.

Please help.
 
Upvote 0
RIZVI said:
I have been able to define Customer, Com & Amount.

But not able to figure out how and where to define Ivec.

Please help.
Click to expand...

Activate Formulas | Name Manager from the ribbon.
Activate New.
Enter Ivec in the box for Name.
Enter the definition

=ROW(Customer)-ROW(INDEX(Customer,1,1))+1

in the Refers to box.
Click OK
 
Upvote 0
Thank you so much. It is working perfectly.

For my understing I want to understand how this codes work.Can you please explain.


RGDS,

Rizvi
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Count number of max values per unique value and column heading?
Replies
5
Views
285
RobP
R
R
  • Question Question
Countifs Multiple Criteria
Replies
3
Views
326
Robot1
R
Z
  • Question Question
Vba - create new sheet and copy date into it.
Replies
1
Views
69
zerofeng
Z
D
  • Solved
Formula for column for (sort of) running count
Replies
7
Views
110
Peter_SSs
Peter_SSs
T
  • Solved
Multiple criteria COUNTIFS with distinct values
Replies
4
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,929
Messages
6,175,459
Members
452,644
Latest member
gjcase

Share this page

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