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
Aladin Akyurek said:
In H7 control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(MATCH($B$2:$B$50,$H$2:$H$3,0)),IF($C$2:$C$50>=H4,IF($D$2:$D$50<=H5,IF($F$2:$F$50=H6,$E$2:$E$50)))))

This is a multiconditional average of hours where one of the conditions is "division 1", a particular division.
Click to expand...

All done!

Thank you!
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Could anyone help I need to do a countif with 2/3 criteria based on a unique order number;

A
 
Upvote 0
Could anyone help I need to do a countif with 2/3 criteria based on a unique order number.
I need to count the numbers of companies different methods of travel to work

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Method[/TD]
[TD]Company name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bike [/TD]
[TD]test 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Walk [/TD]
[TD]test 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bus [/TD]
[TD]test 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Walk [/TD]
[TD]test 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Car [/TD]
[TD]test 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Car [/TD]
[TD]test 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Bike [/TD]
[TD]test 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Bus [/TD]
[TD]test 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Bus [/TD]
[TD]test 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Car [/TD]
[TD]test 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Car[/TD]
[TD]test 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



My results using the above 3 companies and 10 employees should look like this

Bike/Walk/Car/Bus - 1
Bus/Car - 1
Car - 1

I have a large spreadsheet of close to 1,000 different companies so need to be able to summarise which method(s) is the most popular
 
Last edited by a moderator:
Upvote 0
Both Test2 & test 3 use Bus/Car, but you show only 1 for that & i for Car.
How do you get those results?
 
Upvote 0
I've gone through a number of pages trying to get this to work with the dataset that I'm working on, but to no success! Hoping that someone may be able to help me out.

I'm trying to populate a table with the unique account numbers by channel by month. The data set that I have looks something like this:

[TABLE="width: 214"]
<tbody>[TR]
[TD]CCYYMM[/TD]
[TD]Account #[/TD]
[TD]Channel[/TD]
[/TR]
[TR]
[TD="align: right"]201701[/TD]
[TD="align: right"]123456[/TD]
[TD]Inbound[/TD]
[/TR]
[TR]
[TD="align: right"]201701[/TD]
[TD="align: right"]123456[/TD]
[TD]Inbound[/TD]
[/TR]
[TR]
[TD="align: right"]201701[/TD]
[TD="align: right"]987654[/TD]
[TD]Inbound[/TD]
[/TR]
[TR]
[TD="align: right"]201701[/TD]
[TD="align: right"]456789[/TD]
[TD]Door to Door[/TD]
[/TR]
[TR]
[TD="align: right"]201701[/TD]
[TD="align: right"]718293[/TD]
[TD]Outbound[/TD]
[/TR]
[TR]
[TD="align: right"]201702[/TD]
[TD="align: right"]951753[/TD]
[TD]Inbound[/TD]
[/TR]
[TR]
[TD="align: right"]201702[/TD]
[TD="align: right"]789123[/TD]
[TD]Outbound[/TD]
[/TR]
[TR]
[TD="align: right"]201702[/TD]
[TD="align: right"]789123[/TD]
[TD]Outbound[/TD]
[/TR]
[TR]
[TD="align: right"]201703[/TD]
[TD="align: right"]319782[/TD]
[TD]Door to Door[/TD]
[/TR]
[TR]
[TD="align: right"]201703[/TD]
[TD="align: right"]319782[/TD]
[TD]Door to Door[/TD]
[/TR]
[TR]
[TD="align: right"]201704[/TD]
[TD="align: right"]468237[/TD]
[TD]Outbound[/TD]
[/TR]
[TR]
[TD="align: right"]201704[/TD]
[TD="align: right"]468237[/TD]
[TD]Outbound[/TD]
[/TR]
[TR]
[TD="align: right"]201704[/TD]
[TD="align: right"]101010[/TD]
[TD]Outbound[/TD]
[/TR]
[TR]
[TD="align: right"]201704[/TD]
[TD="align: right"]852456[/TD]
[TD]Inbound[/TD]
[/TR]
[TR]
[TD="align: right"]201704[/TD]
[TD="align: right"]741369[/TD]
[TD]Door to Door[/TD]
[/TR]
[TR]
[TD="align: right"]201704[/TD]
[TD="align: right"]327869[/TD]
[TD]Inbound[/TD]
[/TR]
</tbody>[/TABLE]

My end table ideally would look like this:

[TABLE="width: 426"]
<tbody>[TR]
[TD]Count of Unique Accounts[/TD]
[TD="align: right"]201701[/TD]
[TD="align: right"]201702[/TD]
[TD="align: right"]201703[/TD]
[TD="align: right"]201704[/TD]
[/TR]
[TR]
[TD]Inbound[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Outbound[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Door to Door[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

All the help is greatly appreciated!!
 
Upvote 0
@KamCheng

[TABLE="class: grid, width: 456"]
<tbody>[TR]
[TD]Count of Unique Accounts[/TD]
[TD]201701[/TD]
[TD]201702[/TD]
[TD]201703[/TD]
[TD]201704[/TD]
[/TR]
[TR]
[TD]Inbound[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Outbound[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Door to Door[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

In B2 control+shift+enter, not just enter, copy across, and down:

=SUM(IF(FREQUENCY(IF(Channel=$A2,IF(CCYYMM=B$1,Account)),Account),1))
 
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
68
zerofeng
Z
D
  • Solved
Formula for column for (sort of) running count
Replies
7
Views
109
Peter_SSs
Peter_SSs
T
  • Solved
Multiple criteria COUNTIFS with distinct values
Replies
4
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,924
Messages
6,175,419
Members
452,640
Latest member
steveridge

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