Count If with multiple criteria and unique values

[dixon1983]

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.

 

[Orange666]

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.

All done!

Thank you!

 

[Aladin Akyurek]

All done!

Thank you!

Great. Thanks for the update.

 

[Legacy 90606]

Could anyone help I need to do a countif with 2/3 criteria based on a unique order number;

A

 

[Legacy 90606]

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

	A	B
1	Method	Company name
2	Bike	test 1
3	Walk	test 1
4	Bus	test 2
5	Walk	test 1
6	Car	test 1
7	Car	test 2
8	Bike	test 1
9	Bus	test 3
10	Bus	test 1
11	Car	test 3
12	Car	test 1

<tbody>
</tbody>

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

 

[Aladin Akyurek]

What does

Bike/Walk/Car/Bus - 1

mean?

 

[Legacy 90606]

What does

Bike/Walk/Car/Bus - 1

mean?

1 company (test 1) employees use bike/walk/car/bus as their mode of transport

 

[Fluff]

Both Test2 & test 3 use Bus/Car, but you show only 1 for that & i for Car.
How do you get those results?

 

[Legacy 90606]

Both Test2 & test 3 use Bus/Car, but you show only 1 for that & i for Car.
How do you get those results?

My mistake bus/car should have an entry of 2 and car and entry of 0

 

[KamCheng]

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:

CCYYMM	Account #	Channel
201701	123456	Inbound
201701	123456	Inbound
201701	987654	Inbound
201701	456789	Door to Door
201701	718293	Outbound
201702	951753	Inbound
201702	789123	Outbound
201702	789123	Outbound
201703	319782	Door to Door
201703	319782	Door to Door
201704	468237	Outbound
201704	468237	Outbound
201704	101010	Outbound
201704	852456	Inbound
201704	741369	Door to Door
201704	327869	Inbound

<tbody>
</tbody>

My end table ideally would look like this:

Count of Unique Accounts	201701	201702	201703	201704
Inbound	2	1		2
Outbound	1	1		2
Door to Door	1		1	1

<tbody>
</tbody>

All the help is greatly appreciated!!

 

[Aladin Akyurek]

@KamCheng

Count of Unique Accounts	201701	201702	201703	201704
Inbound	2	1	0	2
Outbound	1	1	0	2
Door to Door	1	0	1	1

<tbody>
</tbody>

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))