# Formula to Count Based on Text/Multiple Criteria

#### AFLeddie

##### Board Regular
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

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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

try this

Excel 2003
ABC
7DREE
8DIEF1
9CREE
10LTEF
11STEE
12
Sheet2
Cell Formulas
RangeFormula
C8=SUMPRODUCT(--(B2:B16="EE"),--(A2:A16="CR"))

Give this a try:

Sheet2
ABC
2CREE4
3LTEF
4STEG
5CREF
6STEG
7CREE
8CREF
9LTEG
10STEG
11DIEF
12CREG
13CREE
14LTEF
15STEG
16DIEE
17CREF
18CREG
19LTEG
20STEF
21CREE
22
23
24------------------------------------
25

</tbody>
Excel 2010

Worksheet Formulas
CellFormula
C2=SUMPRODUCT((A2:A21="CR")*(B2:B21="EE"))

</tbody>

<tbody>
</tbody>

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

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

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)

Replies
0
Views
271
Replies
1
Views
252
Replies
8
Views
273
Replies
9
Views
265
Replies
2
Views
494

1,203,435
Messages
6,055,362
Members
444,781
Latest member
rishivar

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back