Count of items using multiple criteria
 

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:V10040))

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

Regards,
KL

"mbparks" wrote in message
...
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.

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

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

mbparks wrote:
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.

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

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

mbparks wrote:
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.

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" wrote:

Hi there,

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

=SUMPRODUCT((I2:I100=1)*(V2:V10040))

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

Regards,
KL

"mbparks" wrote in message
...
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.

Then try:

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

or

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

Regards,
KL

"mbparks" wrote in message
...
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" wrote:

Hi there,

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

=SUMPRODUCT((I2:I100=1)*(V2:V10040))

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

Regards,
KL

"mbparks" wrote in message
...
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.

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

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

or

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

Regards,
KL

------------
"KL" wrote in message
...
Then try:

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

or

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

Regards,
KL

"mbparks" wrote in message
...
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" wrote:

Hi there,

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

=SUMPRODUCT((I2:I100=1)*(V2:V10040))

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

Regards,
KL

"mbparks" wrote in message
...
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.

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

"mbparks" wrote:

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.