Countif function with multiple column criteria?

[Ryan]

Hello...

I'm hoping someone, much smarter than I, can help me find a solution to my
problem.

Problem:
My spreasheet looks like this (two columns):
1 day
2 months
5 years
6 weeks
5 years
7 days
1 month
3 years
1 day
6 weeks

I want to use a count function to count the number of times 5 years occurs,
and I am having a hard time getting a nestedif function to make this happen.

Any suggesstions?

 

[N harkawat]

=SUMPRODUCT(--(A2:A500=5),--(B2:B500="years"))

 

[Francis]

Assuming your data are in Col a and Col B
you need to add a helper column
in Col C, place this formula in C2 and copy down
=A2&" "&B2

oe you can place this in a faraway col and hide it

in col D, place this formula
=COUNTIF(C2:C11,"5 years")

adjust this to your range
--
Hope this is helpful
Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis
Am not a greek but an ordinary user trying to assist another

 

[Ryan]

Thanks for the help!

Now if i add a third column to spreadsheet....like this:

5 years tall
5 year short
3 months tall/sick
5 years short/sick
5 years sick/tall

and I enter the function:

=sumproduct(--(a1:a500=5),--(b1:b500="y*"),--(c1:c500="*tall*"))

I get a #NUM error...

I need to search with "y*" because sometimes year maybe spelled incorectly,
also I need to search the third column for "*tall*" as it may be with
additional text seperated by a forward slash (/).

Any additional suggestions?

 

[Ryan]

Thanks for the suggestion Francis, but I'd rather not concatenate the columns
into a new column.

 

[T. Valko]

If you're using Excel 2007...

=COUNTIFS(A2:A11,5,B2:B11,"years")

Better to use cells to hold the criteria:

D2 = 5
E2 = years

=COUNTIFS(A2:A11,D2,B2:B11,E2)

 

[RagDyer]

One way:

=SUMPRODUCT((A1:A500=5)*(LEFT(B1:B500)="y")*(ISNUMBER(SEARCH("tall",C1:C500))))

 

[T. Valko]

sometimes year maybe spelled incorectly

What? Are you kidding? Someone doesn't know how to spell a 4 letter word
like "yeer"? <VBG>

Try this:

=SUMPRODUCT(--(A1:A500=5),--(LEFT(B1:B500)="y"),--(ISNUMBER(SEARCH("tall",C1:C500))))

You can't directly use wildcards in SUMPRODUCT.

 

[Dave Peterson]

And what happens if I misspell year as: eyar?

Or spell month as: mynth.

It may be better to look through the data and fix those spelling mistakes before
trusting anything that comes out of it.