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.

[TABLE="width: 428"]
<tbody>[TR]
[TD]File No.[/TD]
[TD]Type[/TD]
[TD]Mode[/TD]
[TD]Region[/TD]
[TD]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]

 

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

[TABLE="width: 428"]
<tbody>[TR]
[TD]File No.[/TD]
[TD]Type[/TD]
[TD]Mode[/TD]
[TD]Region[/TD]
[TD]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>[/TABLE]

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:

[table="width: 500, align: left"]
[tr]
[td]A[/td]
[td]B[/td]
[/tr]
[tr]
[td]Company[/td]
[td]Location[/td]
[/tr]
[tr]
[td]Ghadir Clothing[/td]
[td]Andalucia[/td]
[/tr]
[tr]
[td]Ghadir Clothing[/td]
[td]Madrid[/td]
[/tr]
[tr]
[td]Unilever[/td]
[td]Barcelona[/td]
[/tr]
[tr]
[td]Anasys Co.[/td]
[td]Castille[/td]
[/tr]
[tr]
[td]Soha Moor Co.[/td]
[td]Barcelona[/td]
[/tr]
[tr]
[td]...[/td]
[td]...[/td]
[/tr]
[/table]
Space separating a separate category of sales that fits within the same company/location constraint.
[table="width: 500, align: left"]
[tr]
[td][/td]
[td][/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Ghadir Clothing[/td]
[td]Andalucia[/td]
[/tr]
[tr]
[td]Ghadir Clothing[/td]
[td]Madrid[/td]
[/tr]
[tr]
[td]Unilever[/td]
[td]Ceuta[/td]
[/tr]
[tr]
[td]Anasys Co.[/td]
[td]Castille[/td]
[/tr]
[tr]
[td]Anasys Co.[/td]
[td]Barcelona[/td]
[/tr]

[/table]

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
Company	Location	Unique Count
Ghadir Clothing	Andalucia
Ghadir Clothing	Madrid
Unilever	Barcelona
Anasys Co.	Castille
Soha Moor Co.	Barcelona
Ghadir Clothing	Andalucia
Ghadir Clothing	Madrid
Unilever	Ceuta
Anasys Co.	Castille
Anasys Co.	Barcelona

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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.