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]

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

Try...

=SUM(SUMIFS(Tons,Type,{"invoice","credit memo"},Region,"latam & carib.",Mode,"exp-air"))

 

[HomeSlice]

Thank you but how come you do not include the File No? I am trying to add tons for non-repeating File No that meet the other three criteria.

File No.	Type	Mode	Region	Tons
M-IMP-AIR1578	Invoice	IMP-AIR	APAC	0.01
M-EXP-SEA142	Bill	EXP-SEA	Europe	(0.04)
M-EXP-AIR2273	Invoice	EXP-AIR	LATAM & Carib.	0.92
M-EXP-AIR2273	Credit Memo	EXP-AIR	LATAM & Carib.	(0.02)
M-EXP-AIR2305	Invoice	EXP-AIR	NORAM	0.07
M-EXP-SEA697	Bill Credit	EXP-SEA	Europe	0.86
M-EXP-SEA697	Invoice	EXP-SEA	LATAM & Carib.	0.04
M-EXP-AIR2285	Invoice	EXP-AIR	LATAM & Carib.	0.65

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

 

[Aladin Akyurek]

Thank you but how come you do not include the File No? I am trying to add tons for non-repeating File No that meet the other three criteria.

File No.	Type	Mode	Region	Tons
M-IMP-AIR1578	Invoice	IMP-AIR	APAC	0.01
M-EXP-SEA142	Bill	EXP-SEA	Europe	(0.04)
M-EXP-AIR2273	Invoice	EXP-AIR	LATAM & Carib.	0.92
M-EXP-AIR2273	Credit Memo	EXP-AIR	LATAM & Carib.	(0.02)
M-EXP-AIR2305	Invoice	EXP-AIR	NORAM	0.07
M-EXP-SEA697	Bill Credit	EXP-SEA	Europe	0.86
M-EXP-SEA697	Invoice	EXP-SEA	LATAM & Carib.	0.04
M-EXP-AIR2285	Invoice	EXP-AIR	LATAM & Carib.	0.65

<tbody>
</tbody>

That would have resulted in 1.57, not in 1.55.

 

[Brittle_Benzene]

Hi,

I've been struggling with this problem all day and after reading all 31 pages of this forum post, I'm still no closer to a solution. So here's my problem: I have a table set up with the following:

A	B
Company	Location
Ghadir Clothing	Andalucia
Ghadir Clothing	Madrid
Unilever	Barcelona
Anasys Co.	Castille
Soha Moor Co.	Barcelona
...	...

Space separating a separate category of sales that fits within the same company/location constraint.

Ghadir Clothing	Andalucia
Ghadir Clothing	Madrid
Unilever	Ceuta
Anasys Co.	Castille
Anasys Co.	Barcelona

So here's the issue. I have a large amount of text data in many columns, but the two columns I posted I need to count. The first column is company name, the second is the location of their manufacture. I need to count a complete amount of companies in this column, but exclude duplicates. So for my example table, the number of countable companies would equal 6, as the second copy of Anasys and Ghadir Clothing is redundant.

Can you help me set up a formula to count all of these individual companies in column A, but exclude duplicates with regards to location in column B? In this case, I wouldn't care if Ghadir is making a separate category of goods in the lower rows as long is it's at the same factories as previously counted in the above rows. I would care however if it was at a new location. If one factory is making multiple goods, I don't care, but if multiple factories are making goods, I do care.

Thanks a lot in advance, I've been riding the struggle bus all day on this one.

-BB-

 

[Eric W]

Hi BB,

Welcome to the forum.

Well done searching for an answer, but here's a piece of advice. If you can't find your answer, it might be better to start a new thread instead of adding a post to the end of a 31-page thread. It might get seen a little easier.

Here's one possibility if I understand you correctly:

	A	B	C
1	Company	Location	Unique Count
2	Ghadir Clothing	Andalucia	7
3	Ghadir Clothing	Madrid
4	Unilever	Barcelona
5	Anasys Co.	Castille
6	Soha Moor Co.	Barcelona
7
8	Ghadir Clothing	Andalucia
9	Ghadir Clothing	Madrid
10	Unilever	Ceuta
11	Anasys Co.	Castille
12	Anasys Co.	Barcelona
13

<tbody>
</tbody>

Sheet7​

Array Formulas Cell Formula C2 {=SUM(IF($A$2:$A$12<>"",IF(MATCH($A$2:$A$12&$B$2:$B$12,$A$2:$A$12&$B$2:$B$12,0)=ROW($A$2:$A$12)-ROW($A$2)+1,1)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

In this case, rows 2,3,4,5,6,10, and 12 are unique. Let me know if this works for you.

 

[Aladin Akyurek]

@Brittle_Benzene

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$12<>"",IF($B$2:$B$12<>"",
    MATCH($A$2:$A$12&"|"&$B$2:$B$12,$A$2:$A$12&"|"&$B$2:$B$12,0))),
    ROW($A$2:$A$12)-ROW($A$2)+1),1))

 

[Brittle_Benzene]

Eric, I owe you a cake or something, that perfectly did the trick. My original equation was the same as yours but I didn't have anything past that second = symbol, as I didn't know what to input there. Thank you so much for your assistance! Also the only reason I posted in this thread was for others like me that stumbled upon it and maybe their question will be answered in one of these pages

Can I ask you, how have you gone about learning excel for yourself to get to this point? I've been struggling to get to a more intuitive sense of programming excel and maybe you were aware of some resources I'm not?

Thanks!
-BB

 

[Aladin Akyurek]

Try to test the proposals also with empty/blanks cells in the B-range.

 

[Brittle_Benzene]

Try to test the proposals also with empty/blanks cells in the B-range.

Yup it works perfectly, and excludes blanks.

-BB

 

[Aladin Akyurek]

Yup it works perfectly, and excludes blanks.

-BB

Exactly. Since you seem to be interested in formula matters, try to uncover why to use something like "|" when concatenation appears necessary.