# Count text column based on Two Grouping Columns

1. ## Count text column based on Two Grouping Columns

Good morning

I have three columns of data:

Column A = ID#
Column B = Week: Week1, Week2, Week3 . . . . up to Week30 for each ID #
Column C = various codes: S, D, NS, A
In Column D, I want to only count the A's for each week for each ID#

A B C D
ID# WEEK CODE A
1 Week1 A 1
1 Week1 D 1
1 Week2 A 2
1 Week2 A 2
1 Week2 S 2
2 Week1 A 2
2 Week1 A 2
2 Week2 A 3
2 Week2 A 3
2 Week2 D 3
2 Week2 A 3

I know I can use pivot tables, but this file will be used in a statistical package so I need to code the data a particular way.

Thanks

Marby

2. ## Re: Count text column based on Two Grouping Columns

a spreadsheet as per the yellow banner, would have been good to see the setup
try
=IF(OR(A2="",B2=B1),"",COUNTIFS(A:A,A2,B:B,B2,C:C,"A"))
and copy down

OR if you need an entry for every row
=IF(A2="","",COUNTIFS(A:A,A2,B:B,B2,C:C,"A"))

3. ## Re: Count text column based on Two Grouping Columns

That second formula worked beautifully. It was exactly what I needed.

Thank you so much!

Marby

4. ## Re: Count text column based on Two Grouping Columns

you are welcome

There are currently 1 users browsing this thread. (0 members and 1 guests)