richh
Board Regular
- Joined
- Jun 24, 2007
- Messages
- 245
- Office Version
- 365
- 2016
I've been trying to figure a formula out to count a cell that relies on two criteria. The cell has to have the word "Funded" in column C in it as well as being a unique number in column B and whose cell in column A is a certain word(s). The forumlas I'm using for column B on sheet1 is =COUNTIF(Sheet2!A:A, "Ford"). For Column C on sheet 1 is =SUM(IF(FREQUENCY(IF(Sheet2!A:A="Ford",Sheet2!B:B ),IF(Sheet2!A:A="Ford",Sheet2!B:B ))>0,1)). I can't figure out how to further filter it down for Num Funded and Not Funded.
I'm trying =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))
Here's the layout (more or less):
Sheet 1
Dealer | Num. People | Num. Apps. | Num. Funded | Not 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
I saw the formulas I'm using on this thread: http://www.mrexcel.com/forum/excel-...el-count-unique-values-matching-criteria.html
I need a formula for if it says "Ford" in sheet2!A:A, if the app. number is unique in sheet2!B:B, and if it says "Funded" in sheet2!C:C. I also need one if it doesn't say "Funded" in sheet2!C:C
Thank you for any help you can give me.
Rich H.
I'm trying =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))
Here's the layout (more or less):
Sheet 1
Dealer | Num. People | Num. Apps. | Num. Funded | Not 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
I saw the formulas I'm using on this thread: http://www.mrexcel.com/forum/excel-...el-count-unique-values-matching-criteria.html
I need a formula for if it says "Ford" in sheet2!A:A, if the app. number is unique in sheet2!B:B, and if it says "Funded" in sheet2!C:C. I also need one if it doesn't say "Funded" in sheet2!C:C
Thank you for any help you can give me.
Rich H.