Count of items using multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
=SUMPRODUCT(--(TypeRange=Type),--(AgeRange>=40))

The ranges cannot be whole columns like I:I in this type of formulas.
 
=SUMPRODUCT(--(TypeRange=Type),--(AgeRange>=40))

The ranges cannot be whole columns like I:I in this type of formulas.
 
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.
 
Then try:

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

or

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

Regards,
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

------------
 
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!
 
Back
Top