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
@jurgen272

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][/tr]
[tr][td]
1​
[/td][td]X[/td][td]Office[/td][td]Month[/td][td]Office[/td][td]APR[/td][td]MAY[/td][/tr]


[tr][td]
2​
[/td][td]
1212100​
[/td][td]Office1[/td][td]APR[/td][td]Office1[/td][td]
3​
[/td][td]
1​
[/td][/tr]


[tr][td]
3​
[/td][td]
1212100​
[/td][td]Office1[/td][td]APR[/td][td]Office2[/td][td]
1​
[/td][td]
1​
[/td][/tr]


[tr][td]
4​
[/td][td]
112122​
[/td][td]Office1[/td][td]APR[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]
5​
[/td][td]
112122​
[/td][td]Office1[/td][td]MAY[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]
6​
[/td][td]
123456789​
[/td][td]Office1[/td][td]APR[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]
7​
[/td][td]
123456789​
[/td][td]Office2[/td][td]APR[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]
8​
[/td][td]
14598763​
[/td][td]Office2[/td][td]MAY[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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

=SUM(IF(FREQUENCY(IF($B$2:$B$8=$D2,IF($C$2:$C$8=E$1,$A$2:$A$8)),$A$3:$A$8),1))
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Aladin Akyurek said:
@jurgen272

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][/tr]
[tr][td]
1​
[/td][td]X[/td][td]Office[/td][td]Month[/td][td]Office[/td][td]APR[/td][td]MAY[/td][/tr]


[tr][td]
2​
[/td][td]
1212100​
[/td][td]Office1[/td][td]APR[/td][td]Office1[/td][td]
3​
[/td][td]
1​
[/td][/tr]


[tr][td]
3​
[/td][td]
1212100​
[/td][td]Office1[/td][td]APR[/td][td]Office2[/td][td]
1​
[/td][td]
1​
[/td][/tr]


[tr][td]
4​
[/td][td]
112122​
[/td][td]Office1[/td][td]APR[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]
5​
[/td][td]
112122​
[/td][td]Office1[/td][td]MAY[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]
6​
[/td][td]
123456789​
[/td][td]Office1[/td][td]APR[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]
7​
[/td][td]
123456789​
[/td][td]Office2[/td][td]APR[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]
8​
[/td][td]
14598763​
[/td][td]Office2[/td][td]MAY[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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

=SUM(IF(FREQUENCY(IF($B$2:$B$8=$D2,IF($C$2:$C$8=E$1,$A$2:$A$8)),$A$3:$A$8),1))
Click to expand...

Yes, this is working like it should be. Thank you very much !
 
Upvote 0
jurgen272 said:
Hmm, it is working when I have numbers in column A, but not if I put text in it. Any solution?
Click to expand...

Because it's meant for items which are numbers...

In E2 control+shift+enter, copy across, and down:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF(1-($A$2:$A$8=""),IF($B$2:$B$8=$D2,IF($C$2:$C$8=E$1,
    MATCH($A$2:$A$8,$A$2:$A$8,0)))),ROW($A$2:$A$8)-ROW($A$2)+1),1))

This admits numbers as well as text etc.
 
Upvote 0
Hello Aladin and Spreedsheet Crusader.
I have followed all of your suggestions above but can't seem to find a way to make the following work. I am working with hundreds of records but below, I use a very small example. I need a unique count of File No whose Type is "Invoice" or "Credit Memo". In the example immediately below, the answer should be 3. Thank you VERY MUCH in advance for your time and help.


[TABLE="width: 332"]
<tbody>[TR]
[TD][Column A]
File No[/TD]
[TD][Column B]
Type[/TD]
[/TR]
[TR]
[TD]EXP-AIR1578[/TD]
[TD]Invoice[/TD]
[/TR]
[TR]
[TD]EXP-AIR1578[/TD]
[TD]Credit Memo[/TD]
[/TR]
[TR]
[TD]EXP-AIR2273[/TD]
[TD]Bill[/TD]
[/TR]
[TR]
[TD]EXP-AIR2305[/TD]
[TD]Bill[/TD]
[/TR]
[TR]
[TD]EXP-AIR2346[/TD]
[TD]Bill[/TD]
[/TR]
[TR]
[TD]EXP-AIR3089[/TD]
[TD]Bill Credit[/TD]
[/TR]
[TR]
[TD]EXP-AIR3453[/TD]
[TD]Credit Memo[/TD]
[/TR]
[TR]
[TD]EXP-AIR3422[/TD]
[TD]Invoice[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]
 
Upvote 0
HomeSlice said:
Hello Aladin and Spreedsheet Crusader.
I have followed all of your suggestions above but can't seem to find a way to make the following work. I am working with hundreds of records but below, I use a very small example. I need a unique count of File No whose Type is "Invoice" or "Credit Memo". In the example immediately below, the answer should be 3. Thank you VERY MUCH in advance for your time and help.


[TABLE="width: 332"]
<tbody>[TR]
[TD][Column A]
File No[/TD]
[TD][Column B]
Type[/TD]
[/TR]
[TR]
[TD]EXP-AIR1578[/TD]
[TD]Invoice[/TD]
[/TR]
[TR]
[TD]EXP-AIR1578[/TD]
[TD]Credit Memo[/TD]
[/TR]
[TR]
[TD]EXP-AIR2273[/TD]
[TD]Bill[/TD]
[/TR]
[TR]
[TD]EXP-AIR2305[/TD]
[TD]Bill[/TD]
[/TR]
[TR]
[TD]EXP-AIR2346[/TD]
[TD]Bill[/TD]
[/TR]
[TR]
[TD]EXP-AIR3089[/TD]
[TD]Bill Credit[/TD]
[/TR]
[TR]
[TD]EXP-AIR3453[/TD]
[TD]Credit Memo[/TD]
[/TR]
[TR]
[TD]EXP-AIR3422[/TD]
[TD]Invoice[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Control+shift+enter, not just enter:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF(1-(FileNo=""),
    IF(ISNUMBER(MATCH(Type,{"invoice","credit memo"},0)),
    MATCH(FileNo,FileNo,0))),Ivec),1))
where the following definitions are implemented using the Name Box and Formulas | Name Manager:

FileNo >> A2:A9

Type >> B2:B9

Ivec >>

Rich (BB code): 
=ROW(FileNo)-ROW(INDEX(FileNo,1,1))+1
 
Upvote 0
Wow. Thank you very much Aladin. You are a true black belt!
large_brucelee330.jpg
<strike></strike>
 
Upvote 0
Hello Aladin
Can you please show me how to combine the above in a function to sum a field. This is the last formula I need to do in order to finish a management report template.
I have the formula to sum the tons =SUMIFS(tons,type,{"Invoice","Credit Memo"},region,"APAC",mode,"M-EXP-SEA")...but....I do not know how to do it for UNIQUE File No.

Objective: Add the tons
Criteria:
- Unique File No.
- Type = "Invoice" or "Credit Memo"
- Mode = "EXP-AIR"
- Region = "LATAM & Carib."

The correct answer should 1.55

[TABLE="width: 486"]
<tbody>[TR]
[TD][A]
File No.[/TD]
[TD]
Type[/TD]
[TD][C]
Mode[/TD]
[TD][D]
Region[/TD]
[TD][E]
Tons[/TD]
[/TR]
[TR]
[TD]M-IMP-AIR1578[/TD]
[TD]Invoice[/TD]
[TD]IMP-AIR[/TD]
[TD]APAC[/TD]
[TD] 0.01 [/TD]
[/TR]
[TR]
[TD]M-EXP-SEA142[/TD]
[TD]Bill[/TD]
[TD]EXP-SEA[/TD]
[TD]Europe[/TD]
[TD] (0.04)[/TD]
[/TR]
[TR]
[TD]M-EXP-AIR2273[/TD]
[TD]Invoice[/TD]
[TD]EXP-AIR[/TD]
[TD]LATAM & Carib.[/TD]
[TD] 0.92 [/TD]
[/TR]
[TR]
[TD]M-EXP-AIR2273[/TD]
[TD]Credit Memo[/TD]
[TD]EXP-AIR[/TD]
[TD]LATAM & Carib.[/TD]
[TD] (0.02)[/TD]
[/TR]
[TR]
[TD]M-EXP-AIR2305[/TD]
[TD]Invoice[/TD]
[TD]EXP-AIR[/TD]
[TD]NORAM[/TD]
[TD] 0.07 [/TD]
[/TR]
[TR]
[TD]M-EXP-SEA697[/TD]
[TD]Bill Credit[/TD]
[TD]EXP-SEA[/TD]
[TD]Europe[/TD]
[TD] 0.86 [/TD]
[/TR]
[TR]
[TD]M-EXP-SEA697[/TD]
[TD]Invoice[/TD]
[TD]EXP-SEA[/TD]
[TD]LATAM & Carib.[/TD]
[TD] 0.04 [/TD]
[/TR]
[TR]
[TD]M-EXP-AIR2285[/TD]
[TD]Invoice[/TD]
[TD]EXP-AIR[/TD]
[TD]LATAM & Carib.[/TD]
[TD] 0.65 [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

Thank you very much.




[TABLE="width: 70"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
 
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,451
Members
452,643
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