Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
=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. |
#4
|
|||
|
|||
=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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Count of Items with certain criteria | Excel Discussion (Misc queries) | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) | |||
Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions | |||
calculate percent from multiple criteria | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |