countif with two conditions

[smithrd6]

I have items which have different ages. For example, bread, donuts, begals with each having an age (days), some are 2 days old, others are over 6. I want to count the number of those items which are listed in the same column by age, i.e., bread less than 5 days, begals between 6 and 10 days. Will a countif will multiple criteria work?

 

[Aladin Akyurek]

I have items which have different ages. For example, bread, donuts, begals with each having an age (days), some are 2 days old, others are over 6. I want to count the number of those items which are listed in the same column by age, i.e., bread less than 5 days, begals between 6 and 10 days. Will a countif will multiple criteria work?

Counting all items with a certain age:

=COUNTIF(AgeRange,5)

Counting per item using a age criterion:

=SUMPRODUCT(--(ItemRange="bread"),--(AgeRange < 5))

 

[T. Valko]

I have items which have different ages. For example, bread, donuts, begals with each having an age (days), some are 2 days old, others are over 6. I want to count the number of those items which are listed in the same column by age, i.e., bread less than 5 days, begals between 6 and 10 days. Will a countif will multiple criteria work?

What version of Excel are you using?

 

[smithrd6]

=SUMPRODUCT(--(ItemRange="bread"),--(AgeRange < 5))
works.....

rather than <5, how to capture between two numbers, i.e. >5 and <10?

 

[Aladin Akyurek]

If you are on Excel 2007 or later, try...

=COUNTIFS(ItemRange,"bread"),AgeRange,"<5")

Or with X2 = bread and Y2 = 5...

=COUNTIFS(ItemRange,X2),AgeRange,"<"&Y2)

 

[Aladin Akyurek]

=SUMPRODUCT(--(ItemRange="bread"),--(AgeRange < 5))
works.....

rather than <5, how to capture between two numbers, i.e. >5 and <10?

Try...

=SUMPRODUCT(--(ItemRange="bread"),--(AgeRange > 5),--(AgeRange < 10))

 

[T. Valko]

If you are on Excel 2007 or later, try...

=COUNTIFS(ItemRange,"bread"),AgeRange,"<5")

Or with X2 = bread and Y2 = 5...

=COUNTIFS(ItemRange,X2),AgeRange,"<"&Y2)

I think you have too many ")".

=COUNTIFS(ItemRange,X2,AgeRange,"<"&Y2)