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.

 

[ramana1983]

C2, copied down:

=IF(A2="configurable",IF(COUNTIFS($A$1:A2,A2)>1,
    COUNTIFS(INDEX($A$1:A1,LOOKUP(9.99999999999999E+307,1/($A$1:A1="configurable"),
    ROW($A$1:A1)-ROW($A$1)+1)):A2,"simple"),""),"")

Thanks for the immediate reply
Worked Like a Gem

Regards
PVR Ramana
Online Shopping India - Fashion apparels for Men and Women

 

[Aladin Akyurek]

Thanks for the immediate reply
Worked Like a Gem
[...]

You are welcome.

 

[markom92]

Good afternoon,

I've been making multiple trys, but had no success so far. I'm trying My list goes as follows:

Formulas per column:

COLUMN O --> =IF(J20="","",IF(OR(J2=0,J2="0"),"To be firmed",WEEKNUM(AI2)+0))
COLUMN P --> =35 (Constant number changed by user according to desired calculation)
COLUMN Q --> =IF(K2="39L",0,MID(L2,8,6))
COLUMN R --> =IF(ISBLANK(L2),"",IF(ISERROR(IF(Q2=0,0,MID(L2,14,2)+0)),1,IF(Q2=0,0,MID(L2,14,2)+0))) // Stands for a part of a whole.
COLUMN S --> {=MAX(IF($Q$2:$Q$15000=Q2,$R$2:$R$15000,))} // Stands for the last part of the whole, which represent the end of the whole.
COLUMN T --> =IF(Q2=0,"",Q2&S2)

EXAMPLE TABLE:
[table="width: 500"]
[tr][td] Planned Week [/td][td] Weeknum [/td] [td] Column L [/td] ----------- [td] Column Q [/td] [td]Column R [/td] [td]Column S[/td] [td]Column T[/td][/tr]
[tr]
[td] 35 [/td] [td] 35[/td][td] 39MN03D0750MN12XGS [/td] [td] 0750MN[/td] [td] 12[/td][td] 22 [/td][td] 0750MN22 [/td][/tr]
[tr]
[td] 35 [/td] [td]35[/td] [td] 39MN03D0750MN12XGS [/td][td] 0750MN [/td] [td] 22[/td][td] 22[/td][td] 0750MN22 [/td]
[/tr]
[tr]

[td]To be firmed [/TD] [td] 35 [/td] [td] 39MN03D0860MN12XGS [/td] [td] 0860MN [/td] [td] 12 [/td] [td] 22 [/td] 0750MN22

[/tr]
[tr]
To be firmed 35 39MN03D0860MN12XGS 0860MN 22 22 0750MN22

[/tr]
35 35 39LA03AA-AD-APF152 0 0
[/table]


My intention is to COUNT how many UNIQUE units for WK 35 I have already Scheduled and which are pending to be Scheduled. Example result:

To be firmed: 10 units (unique)
Firmed: 7 units.

My Excel file runs over 15,000 different cells and have two different worksheets. Would appreciate any help on the matter.

 

[Aladin Akyurek]

@markom92

Care to post a small data sample (not formulas) along with the expected result(s)?

 

[markom92]

@markom92

Care to post a small data sample (not formulas) along with the expected result(s)?

[table="width: 500"]
[tr][td]Material[/td] [td]Material entered[/td] [td]SO Creation date[/td][td]Planned or Week Production[/td][td] Weeknum[/td] [td]Odometer [/td][td]Section [/td][td]Max Number[/td][td] Machine Count[/td][/tr]
[tr][td]63X [/td][td]63XN06DL3255313SXQ [/td][td]6/30/2015 [/td] [td]37 [/td][td][/td] [td]L32553 [td]13[/td] [td]33[/td] [td]L3255333[/td][/tr]
[tr][td]63X [/td][td]63XN06DL3255323SXQ[/td] [td]6/30/2015 [/td] [td]37 [/td][td] [/td] [td]L32553 [td]23[/td] [td]33[/td] [td]L3255333[/td][/tr]
[tr][td]63X [/td][td]63XN06DL3255333SXQ [/td][td]6/30/2015 [/td] [td]37 [/td] [td] [/td][td]L32553 [td]33[/td] [td]33[/td] [td]L3255333[/td][/tr]
[tr][td]63X[/td] [td]63XN06DL3255413SXQ[/td] [td]6/30/2015 [/td] [td]37 [/td][td] [/td] [td]L32554 [td]13[/td] [td]33[/td] [td]L3255433[/td][/tr]
[tr][td]63X[/td] [td]63XN06DL3255423SXQ[/td] [td]6/30/2015 [/td] [td]37 [/td] [td] [/td][td]L32554 [td]23[/td] [td]33[/td] [td]L3255433[/td][/tr]
[tr][td]63X[/td] [td]63XN06DL3255433SXQ [/td][td]6/30/2015 [/td] [td]37 [/td] [td] [/td][td]L32554 [td]33[/td] [td]33[/td] [td]L3255433[/td][/tr]
[tr][td]63X[/td] [td]63XN08D023B2011XGS [/td][td]7/21/2015 [/td] [td]To be firmed [/td] [td]40 [/td][td]023B20 [td]11[/td] [td]11[/td] [td]023B2011[/td][/tr]
[tr][td]63X[/td] [td]63XN08D023B2011XGS [/td][td]7/21/2015 [/td] [td]To be firmed [/td][td]40 [/td] [td]023B20 [td]11[/td] [td]11[/td] [td]023B2011[/td][/tr]
[/table]

Expected result table:

[table="width: 500"]
[tr][td]Week[/td] [td]Firm [/td][td]Planned[/td][/tr]
[tr][td]35[/td][/tr]
[tr][td]36[/td] [/tr]
[tr][td]37[/td][/tr]
[tr][td]38[/td][/tr]
[tr][td]39[/td][/tr]
[tr][td]40[/td][/tr]
[/table]

 

[Aladin Akyurek]

[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[table="width: 500"]
[tr] [td]Firm [/td][td]Planned[/td][/tr]
[tr]35[/tr]
[tr]36 [/tr]
[tr]37[/tr]
[tr]38[/tr]
[tr]39[/tr]
[tr]40[/tr]

Try rather to use one of the methods: Attachments or https://app.box.com/s/soezox25h3w0q5s4rcyl for posting a small sample along with the desired result(s).

 

[markom92]

Aladin,
Didn't quite get the tutorial. Nevertheless, the table's up (edited on my old post). Is that helpful enough?

 

[Aladin Akyurek]

Aladin,
Didn't quite get the tutorial. Nevertheless, the table's up (edited on my old post). Is that helpful enough?

What is [td]13?

What is the expected result for week 37 under Firm and under Planned?

 

[markom92]

What is [td]13?

What is the expected result for week 37 under Firm and under Planned?

Work Network is blocking Image Sharing. Later today I'll share image of the problem for documentation.

[Td] 13 [/TD] is referring to column "Section", whereas the two-digit number shown on the table refers to 'Max number' (as a result of a MAX/IF formula. Same goes for all the other numbers in the criteria.

Expected result in this particular example would be:

WK35 - Firm (5) / Planned (0)
WK36 - Firm (3) / Planned (0)
WK37 - Firm (2) / Planned (0)
WK38 - Firm (0) / Planned (0)
WK39 - Firm (0) / Planned (0)
WK40 - Firm (0) / Planned (1)

 

[markom92]

Work Network is blocking Image Sharing. Later today I'll share image of the problem for documentation.

[Td] 13 [/TD] is referring to column "Section", whereas the two-digit number shown on the table refers to 'Max number' (as a result of a MAX/IF formula. Same goes for all the other numbers in the criteria.

Expected result in this particular example would be:

WK35 - Firm (5) / Planned (0)
WK36 - Firm (3) / Planned (0)
WK37 - Firm (2) / Planned (0)
WK38 - Firm (0) / Planned (0)
WK39 - Firm (0) / Planned (0)
WK40 - Firm (0) / Planned (1)

Aladin,
Problem was solved with the following array formula:

{=SUM(IF(FREQUENCY(IF(A2:A23="63X",IF(E2:E23=X21,MATCH(J2:J23,J2:J23,0))),MATCH(J2:J23,J2:J23,0))>0,1))}

Where X21 is where the desired week number is.