Function/Formula in Excel to count Unique values matching criteria

[mando415]

Hi - I have a worksheet. I would like to count unique number of "Trans" in column A only if value of cells in column B "Type" equals "Return". In example below, I would want to see the value "3" as total.

I am banging my head over this and would appreciate any advise.

Trans Type
1 Return
2 Return
2 Return
3 Exch
4 Exch
5 Return
5 Return

Thank you

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF(B2:B8="Return",A2:A8),IF(B2:B8="Return",A2:A8))>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

 

[mando415]

Domenic - Thank you! Works perfectly. Will you please tell when/why I need to use CONTROL+SHIFT+ENTER? This will be part of a template that will be updated with new data weekly. Do I need to perform this each time?

 

[Domenic]

When a formula is confirmed with CONTROL+SHIFT+ENTER, it tells Excel that we're dealing with an array formula. The formula only needs to be confirmed this way when either entering or editing the formula.

 

[mando415]

Try...

=SUM(IF(FREQUENCY(IF(B2:B8="Return",A2:A8),IF(B2:B8="Return",A2:A8))>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

If I wanted to add another condition that looked at Column C for a specific criteria "OK", how could i add this to this formula?

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF(B2:B8="Return",IF(C2:C8="OK",A2:A8)),IF(B2:B8="Return",IF(C2:C8="OK",A2:A8)))>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

 

[ezeakial]

Try...

=SUM(IF(FREQUENCY(IF(B2:B8="Return",IF(C2:C8="OK",A2:A8)),IF(B2:B8="Return",IF(C2:C8="OK",A2:A8)))>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

This is almost the exact formula I need as well, although what I am counting is text and not numbers so giving me a result of zero. Any thoughts on how to tweak this so that it will count my text?

 

[jocheng]

This is almost the exact formula I need as well, although what I am counting is text and not numbers so giving me a result of zero. Any thoughts on how to tweak this so that it will count my text?

Solution to that is:
= SUM(IF(FREQUENCY(IF(B2:B8="Return",MATCH(A2:A8,A2:A8,0)),IF(B2:B8="Return",MATCH(A2:A8,A2:A8,0)))>0,1))

And as pointed before: ctrl+shift+enter

 

[richh]

Try...

=SUM(IF(FREQUENCY(IF(B2:B8="Return",IF(C2:C8="OK",A2:A8)),IF(B2:B8="Return",IF(C2:C8="OK",A2:A8)))>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

I'm trying something like this too. Mine doesn't work though. I'm trying to count, on sheet1, the number of cells marked "Funded" on sheet2!C:C that are associated with cells marked "Ford" in Sheet2!A:A as well as those that are not duplicates in sheet2!B:B

=SUM(IF(FREQUENCY(IF(Sheet2!A:A="Ford",IF(Sheet2!C:C="Funded",Sheet2!B:B)),IF(Sheet2!A:A="Ford",IF(Sheet2!C:C="Funded",Sheet2!B:B ))>0,1))

It says I have too many arguments.

I've tried =COUNTIFS(Sheet2!A:A,"Ford", Sheet2!C:C,"Funded") , but I can't figure out how to count only those that are not duplicates in sheet2!B:B I want to say it'd be something like ..."Funded")/countif(sheet2!B:B""), but I don't know.

Here's the table:

Sheet 1

Dealer | Num. People | Num. Apps. | Funded?
Ford |3|2||
Chevy |3|3||
Buick |3|2||


Sheet 2

Dealer | App. Num. | Funded?
Ford |1 |Funded
Ford |1 |Funded
Ford |2 |Pending
Chevy |3 |Pending
Chevy |4 |Pending
Chevy |5 |Funded
Buick |6 |Funded
Buick |6 |Funded
Buick |7 |Pending

 

[Aladin Akyurek]

This is almost the exact formula I need as well, although what I am counting is text and not numbers so giving me a result of zero. Any thoughts on how to tweak this so that it will count my text?

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A8<>"",IF(B2:B8="Return",IF(C2:C8="OK",
  MATCH("~"&A2:A8,A2:A8&"",0)))),ROW(A2:A8)-ROW(A2)+1),1))