# COUNTIFS or SUMPRODUCT?

#### jakulski

##### New Member
I have a massive workbook covering +/-50,000 rows and 30 columns. I’m looking for guidance for four scenarios, which are listed below. Thank you in advance!

• For a given column, T, U, or V, I need to count cells that contain AR3, BA1, BA3, BA5, HO* (HO* represents HO1, HO2, and HO3), WC1, WP0, WP1, WP2, WP4, WP5, WP6, WP7, WP8, WP9, WS1, WT1, WV2, WV3, WV8, WX5, or WX9, as long as any one of those is not also included in the same row of either of the two other columns.
• For Colum T, the answer would be 9; for Column U, the answer would be 2

• For a given column, T, U, or V, I need to count cells that contain AR3, BA1, BA3, BA5, HO*, WC1, WP0, WP1, WP2, WP4, WP5, WP6, WP7, WP8, WP9, WS1, WT1, WV2, WV3, WV8, WX5, or WX9, as long as any one of those is also included in the same row of either of the two other columns.
• For Column T, the answer would be 4; for Column V, the answer would be 4

• For three columns together, count the rows in which at least one of the following populates the cell: AR3, BA1, BA3, BA5, HO*, WC1, WP0, WP1, WP2, WP4, WP5, WP6, WP7, WP8, WP9, WS1, WT1, WV2, WV3, WV8, WX5, or WX9. Note: HO* encompasses HO1, HO2, and HO3.
• The answer for this would be 16

• For each category in Column S (A, B, C, D, E), count number of rows in which at least one of the following populates the cell: AR3, BA1, BA3, BA5, HO*, WC1, WP0, WP1, WP2, WP4, WP5, WP6, WP7, WP8, WP9, WS1, WT1, WV2, WV3, WV8, WX5, or WX9.
• For category A, and answer would be 3, and for category B, the answer would be 1.

 S T U V A WP0 WP8 C WT2 WT1 WV3 E WP0 E WP5 BA3 A WP5 AT2 D AT5 WP5 A AR2 BA6 AL1 C WP5 AT5 D WP6 DP1 WP5 D DR5 AR3 E WP5 DR8 DP1 A WP5 HO2 WP1 B HO4 BU2 DS3 B WP5 DX1 A WP5 TB2 DR5 A WP5 TB4 E WP5 TB5 WV3 C WP5 TH3 AT2

<tbody>
</tbody>

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### thisoldman

##### Well-known Member
I think some of the totals you posted were incorrect. For the first conditional count I came up with 8, 2, and zero. The second conditional count is 16. The conditional counts for tokens A through E are 5, 1, 3, 3, and 4.

I used a defined name to hold the search list, The first table is set up to determine if the item in columns T, U or V is in the search list.

The single-cell formula solutions are in the "Total" row, below:

1,109,336
Messages
5,528,104
Members
409,802
Latest member
joeino

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...