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.

 

[RIZVI]

Hi Aladin,

I am preparing yet another list and seek your help.

I have a Customer Master List in column C. Against each customer either C or S is entered in Column to mark Cash or credit customer. In column CA I want list of only Cash Customer. I am using {=IFERROR(INDEX(CustomerMaster,SMALL(IF(FREQUENCY(IF(CustomerMaster<>"",MATCH(CustomerMaster&"|"&ComMaster,CustomerMaster&"|"&ComMaster,0)),IvecMaster),IvecMaster), ROWS(CA$26:CA26))),"")}

but I am getting cash and credit customers.

What changes are required so that I can get only Cash Customers.

Thanks in anticipation,


RGDS

Rizvi

 

[Aladin Akyurek]

note: this worked "=SUM(IF(FREQUENCY(IF(fruit=$E5,IF(month=G$2,IF(grade<>"", MATCH(grade,grade,0)))),ROW(grade)-ROW($B$2)+1),1))"
(as an array)

which is brillaint!! (although i still don't understand $B$2)!

That's almost right. See post #104 for completeness. The following link provide an explanation/description of a similar formula:
http://www.mrexcel.com/forum/excel-...ing-sum-if-frequency-match-2.html#post3156949

 

[Aladin Akyurek]

Hi Aladin,

I am preparing yet another list and seek your help.

I have a Customer Master List in column C. Against each customer either C or S is entered in Column to mark Cash or credit customer. In column CA I want list of only Cash Customer. I am using {=IFERROR(INDEX(CustomerMaster,SMALL(IF(FREQUENCY(IF(CustomerMaster<>"",MATCH(CustomerMaster&"|"&ComMaster,CustomerMaster&"|"&ComMaster,0)),IvecMaster),IvecMaster), ROWS(CA$26:CA26))),"")}

but I am getting cash and credit customers.

What changes are required so that I can get only Cash Customers.

Thanks in anticipation,


RGDS

Rizvi

Not sure where cash/credit marks are entered...

 

[RIZVI]

in another column. Customers are in column C and Cash Credit denoted by C & S respectively are entered in column E.

The list of ONLY cash customers is being required in column CA.

RGDS,

Rizvi

 

[Aladin Akyurek]

in another column. Customers are in column C and Cash Credit denoted by C & S respectively are entered in column E.

The list of ONLY cash customers is being required in column CA.

RGDS,

Rizvi

We want a customer in CA if the corresponding value in E is either (1) C or S or (2) just C or (3) just S or (4) C & S. Which case is it?

 

[RIZVI]

We want customer when the value is E is just C.

 

[Aladin Akyurek]

We want customer when the value is E is just C.

Let's also name the range in E of Master as CreditMaster and in invoke in CA...

Control+shift+enter, not just enter, and copy down:

=IFERROR(
  INDEX(CustomerMaster,SMALL(IF(FREQUENCY(IF(CustomerMaster<>"",IF(CreditMaster="C",
   MATCH(CustomerMaster&"|"&ComMaster,CustomerMaster&"|"&ComMaster,0))),IvecMaster),
   IvecMaster),ROWS(CA$26:CA26))),"")

 

[RIZVI]

Thank you so much. It is working beautifully.

You are a genius.

RGDS,


Rizvi

 

[Aladin Akyurek]

Thank you so much. It is working beautifully.

You are a genius.

RGDS,


Rizvi

You are welcome. Thanks for providing feedback.

 

[vxk5018]

Hi,

I was hoping that you could help me with my problem. I think my problem requires a similar solution to this thread.

Below is a sample table. I am looking for a formula that calculates changeovers. A changeover is a change from one SKU to another SKU or one size to another size. A minor changeover is a change from SKU to SKU but staying with the same size. A major changeover is a change from size to size (which inherently is also a SKU change). A minor changeover takes 6 hours and a major changeover takes 32 hours.

If there is 0 production for a month, the SKU will not be produced. In the example below, only 4 SKUs will be running in October. The assumption is that the plant will change SKUs in a rational fashion so the plant would produce all SKU's with size 4 back to back before moving onto a SKU with a different size.

Based on the example below, there are 4 total changeovers, 2 that are major and 2 that are minor. I figured out that I could do a simple countif >0 to calculate total changeovers but I can't figure out how to count the major changeovers which is counted based on 2 conditions: the production for that SKU is over 0 and there is a size change.

I am hoping you can help. Please let me know what other information you may need.

Thanks,
Vlad


[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Size[/TD]
[TD]Oct 14 Production[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0.5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]