Count of items using multiple criteria

[Guest]

I am working on a spreadsheet that tracks when an item is requested, the type
of request it is, when it returns and the age of the outstanding items.
I have used formulas to automatically enter the type of item being requested
and the age of the outstanding items.
What I want to do is count the number of outstanding item#1s that are over
40 days old. The type of item is in Col. I and the age of o/s items is Col.
V.
I've tried to use the sumproduct formula but I'm not getting the right totals.
Help is greatly appreciated.

 

[KL]

Hi there,

The way you describe it the following formula should work, I guess:

=SUMPRODUCT((I2:I100=1)*(V2:V100>40))

unless any of your data (type or age) are in text format of course.

Regards,
KL

 

[Aladin Akyurek]

=SUMPRODUCT(--(TypeRange=Type),--(AgeRange>=40))

The ranges cannot be whole columns like I:I in this type of formulas.

 

[Aladin Akyurek]

=SUMPRODUCT(--(TypeRange=Type),--(AgeRange>=40))

The ranges cannot be whole columns like I:I in this type of formulas.

 

[Guest]

This is giving me the total number of each item.
The items are called 859 and 181.
I should also metion that the outstanding column will only have a number if
the item is outstanding (there is no date in the returned column). If it has
returned I have set the formula to leave the cell blank.

 

[KL]

Then try:

=SUMPRODUCT((I2:I100=181)*(V2:V100<>""))

or

=SUMPRODUCT(--(I2:I100=181),--(V2:V100<>""))
as per Aladin's post.

Regards,
KL

 

[KL]

Having re-read both of your posts I guess you may need this:

=SUMPRODUCT((I2:I100=181)*(V2:V100<>"")*(V2:V100>40))

or

=SUMPRODUCT(--(I2:I100=181),--(V2:V100<>""),--(V2:V100>40))

Regards,
KL

------------

 

[Guest]

I got it! I had to use 3 arrays (I2:i100=181, v2:v100<>"" and v2:v100>40)
and it gave me the right totals.
I can never thank you enough. This has been driving me crazy for weeks now.
Thank you!