Formula to Count Based on Text/Multiple Criteria

[AFLeddie]

Hey All!

I am in need of some assistance - I can't seem to get a formula to work. I am trying to count the number of times "EE" shows up in a range only if "CR" is listed in that row within another range. For simplicity Column A has a list with the following values scattered from A2 through A100: CR, LT, ST, DI. Column B has a list with the following values scattered from B2 through B100: EE, EF, EG.

I need to know how many EE's are listed in column B if CR is on the same line in column A. Any help is greatly appreciated!

E

 

[c_m_s_jr]

Hey All!

I am in need of some assistance - I can't seem to get a formula to work. I am trying to count the number of times "EE" shows up in a range only if "CR" is listed in that row within another range. For simplicity Column A has a list with the following values scattered from A2 through A100: CR, LT, ST, DI. Column B has a list with the following values scattered from B2 through B100: EE, EF, EG.

I need to know how many EE's are listed in column B if CR is on the same line in column A. Any help is greatly appreciated!

E

TRY:
=SUMPRODUCT(--(A2:A100="CR"),--(B2:B100="EE"))

Or look at using COUNTIFS if you have 2007 or later

 

[texasalynn]

try this

Excel 2003
	A	B	C
7	DR	EE
8	DI	EF	1
9	CR	EE
10	LT	EF
11	ST	EE
12
Sheet2

Cell Formulas
Range	Formula
C8	=SUMPRODUCT(--(B2:B16="EE"),--(A2:A16="CR"))

 

[Jeff Meyers]

Give this a try:

Sheet2

	A	B	C
2	CR	EE	4
3	LT	EF
4	ST	EG
5	CR	EF
6	ST	EG
7	CR	EE
8	CR	EF
9	LT	EG
10	ST	EG
11	DI	EF
12	CR	EG
13	CR	EE
14	LT	EF
15	ST	EG
16	DI	EE
17	CR	EF
18	CR	EG
19	LT	EG
20	ST	EF
21	CR	EE
22
23
24	------------	------------	------------
25

<thead>
</thead><tbody>
</tbody>

Excel 2010

Worksheet Formulas Cell Formula C2 =SUMPRODUCT((A2:A21="CR")*(B2:B21="EE")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[AFLeddie]

And I appreciate everyone's feedback, I didnt have the SUMProduct formula correct. Thanks again for your help!!

 

[Jeff Meyers]

Good to hear that it worked. Thank you for providing feedback.

 

[T. Valko]

Hey All!

I am in need of some assistance - I can't seem to get a formula to work. I am trying to count the number of times "EE" shows up in a range only if "CR" is listed in that row within another range. For simplicity Column A has a list with the following values scattered from A2 through A100: CR, LT, ST, DI. Column B has a list with the following values scattered from B2 through B100: EE, EF, EG.

I need to know how many EE's are listed in column B if CR is on the same line in column A. Any help is greatly appreciated!

E

If you're using Excel 2007 or later...

=COUNTIFS(A2:A100,"CR",B2:B100,"EE")

Better to use cells to hold the criteria:

D2 = CR
E2 = EE

=COUNTIFS(A2:A100,D2,B2:B100,E2)