Counting Function referencing both numbers and phrases

**akhockey** · 01-09-2012, 02:58 PM

I am attempting to craft a spreadsheet that will count certain numbers in columns when a phrase appears in another column but same row.

The essential information is in two Columns, A & B. Column A will list words such as "ABC", "Water", "PK". Column B will list years.

For example, I want to count how many times 2004 appears where ABC is in the same row.

I can then modify the formula to count how many "ABC" there are or sort by different years.

I have used countif for sorting this information by a single variable, i just don't know how to do it for multiple variable in multiple columns or cells. This spreadsheet is approx. 300 rows. Any help is appreciated. Thanks.

**Ron Coderre** · 01-09-2012, 03:02 PM

With your lists in Col_A and Col_B
and
C1: a word to find....ABC
D1: a year to find.....2004

This regular formula returns the count of that combination:

E1: =SUMPRODUCT((A1:A100=C1)*(B1:B100=D1))

Is that something you can work with?

**akhockey** · 01-09-2012, 05:26 PM

Not so much as it does not check if ABC is in the same row. All that function does is add the two occurances together.

**Haseeb A** · 01-09-2012, 05:38 PM

Hello akhockey,

Did you try exactly as Ron said? That will give you the count.

A2:A300 contains words,
B2:B300 contains years

C1, Enter ABC
D1, Enter 2004

Then,

=SUMPRODUCT((A2:A300=C1)*(B2:B300=D1))

This will count both ABC & 2004 are in same row in respective column.. If you have dates in Col_b & formatted as yyyy, then this wouldn't work.

**akhockey** · 01-09-2012, 06:06 PM

Nope, i didn't try it exactly. I was putting in the 'year' or 'ABC' inside the formula and it was kicking out errors and wrong numbers. This works now, i'll need to figure a way to reference the year, as this information is going under a column that is labeled for when service needs to be performed (aka a future year, not the year it was last done). I appreciate the help and the clarification.

**Haseeb A** · 01-09-2012, 06:17 PM

FYI, You can also use Pivot Table.

**akhockey** · 01-09-2012, 06:44 PM

I don't know anything about Pivot Tables yet but i'll probably end up learning them due to this and some other upcoming projects. thanks again for the assistance. the solution ended up being much simpler than I thought it would.