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.

 

[Aladin Akyurek]

@jurgen272

Row\Col	A​	B​	C​	D​	E​	F​
1​	X	Office	Month	Office	APR	MAY
2​	1212100​	Office1	APR	Office1	3​	1​
3​	1212100​	Office1	APR	Office2	1​	1​
4​	112122​	Office1	APR
5​	112122​	Office1	MAY
6​	123456789​	Office1	APR
7​	123456789​	Office2	APR
8​	14598763​	Office2	MAY

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

 

[jurgen272]

@jurgen272

Row\Col	A​	B​	C​	D​	E​	F​
1​	X	Office	Month	Office	APR	MAY
2​	1212100​	Office1	APR	Office1	3​	1​
3​	1212100​	Office1	APR	Office2	1​	1​
4​	112122​	Office1	APR
5​	112122​	Office1	MAY
6​	123456789​	Office1	APR
7​	123456789​	Office2	APR
8​	14598763​	Office2	MAY

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

Yes, this is working like it should be. Thank you very much !

 

[Aladin Akyurek]

Yes, this is working like it should be. Thank you very much !

You are welcome.

 

[jurgen272]

You are welcome.

Hmm, it is working when I have numbers in column A, but not if I put text in it. Any solution?

 

[Aladin Akyurek]

Hmm, it is working when I have numbers in column A, but not if I put text in it. Any solution?

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

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

=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.

 

[HomeSlice]

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.

[Column A] File No	[Column B] Type
EXP-AIR1578	Invoice
EXP-AIR1578	Credit Memo
EXP-AIR2273	Bill
EXP-AIR2305	Bill
EXP-AIR2346	Bill
EXP-AIR3089	Bill Credit
EXP-AIR3453	Credit Memo
EXP-AIR3422	Invoice

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[Aladin Akyurek]

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.

[Column A] File No	[Column B] Type
EXP-AIR1578	Invoice
EXP-AIR1578	Credit Memo
EXP-AIR2273	Bill
EXP-AIR2305	Bill
EXP-AIR2346	Bill
EXP-AIR3089	Bill Credit
EXP-AIR3453	Credit Memo
EXP-AIR3422	Invoice

<tbody>
</tbody>

Control+shift+enter, not just enter:

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

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

 

[HomeSlice]

Wow. Thank you very much Aladin. You are a true black belt!

<strike></strike>

 

[Aladin Akyurek]

Wow. Thank you very much Aladin. You are a true black belt!
[...]

You are welcome.

 

[HomeSlice]

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

[A] File No.

Type

<tbody>
[TD][C]
Mode[/TD]
[TD][D]
Region[/TD]
[TD][E]
Tons[/TD]

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

Thank you very much.

<tbody>
</tbody><colgroup><col></colgroup>