Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count rows based on multiple criteria
Frank,
I've tried your suggestion and it seems to be working. I'm just coming across a couple of snags. All my data in on sheet 1. I want my calculations to occur on sheet 2. I can get your formula to work on sheet 1, where the data is stored, but when I try in on sheet 2, where it references the data on sheet 1, I keep getting an error. This is the formula I am using. Do you see a problem with it? =SUMPRODUCT(--(('749 Open-Pending Inventory'!A2:A99999)=1),--(('749 Open-Pending Inventory'!A2:A99999)<=13)) If there is an issue, how do I correct it? Second question I have is regarding how to excluded certain values from my count. For example, using the above calculation, I also want to exclude any rows that have a value in Column "B" of, "small", or "medium". I didn't quite understand your second formula. How would this work? Brendan "Frank Kabel" wrote: Hi try =SUMPRODUCT(--(A1:A10=1),--(A1:A10<=7),--(B1:B10="Johns"),--(C1:C10="Open")) and =SUMPRODUCT(--(A1:A10=1),--(A1:A10<=7),--(B1:B10="Johns"),--(C1:C10="Open"),D1:D10) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html "Murph" wrote: I'm having trouble figuring out a way to count specific rows in a database I have, using multiple variables. For example. I have column A, B, and C. In column "A" are number values from 1-10. In Column "B" are peoples names, and in Column "C" are other text values. I want to figure out a way to count all the rows that have the numbers 1-3 in column "A", the same name in column "B", and the same text value in column "C". For example all the rows that have a value between 1 and 7 in column A, value of "Jones" in Column B, and a value of "Open" in column C. In additions to that I also need to come up with a formula that does what I described above, but in addition, subtracts and rows that have a specific value in Column "D". I've tried using multiple variations of the COUNT, DCOUNT, SUM, and DSUM functions but have had no luck. Any help would be appreciated. -- Brendan |
#2
|
|||
|
|||
Hi
yes :-) Excel does not have 99999 rows :-). Try: =SUMPRODUCT(--('749 Open-Pending Inventory'!A2:A9999=1),--('749 Open-Pending Inventory'!A2:A9999<=13)) -- Regards Frank Kabel Frankfurt, Germany "Murph" schrieb im Newsbeitrag ... Frank, I've tried your suggestion and it seems to be working. I'm just coming across a couple of snags. All my data in on sheet 1. I want my calculations to occur on sheet 2. I can get your formula to work on sheet 1, where the data is stored, but when I try in on sheet 2, where it references the data on sheet 1, I keep getting an error. This is the formula I am using. Do you see a problem with it? =SUMPRODUCT(--(('749 Open-Pending Inventory'!A2:A99999)=1),--(('749 Open-Pending Inventory'!A2:A99999)<=13)) If there is an issue, how do I correct it? Second question I have is regarding how to excluded certain values from my count. For example, using the above calculation, I also want to exclude any rows that have a value in Column "B" of, "small", or "medium". I didn't quite understand your second formula. How would this work? Brendan "Frank Kabel" wrote: Hi try =SUMPRODUCT(--(A1:A10=1),--(A1:A10<=7),--(B1:B10="Johns"),--(C1:C10="O pen")) and =SUMPRODUCT(--(A1:A10=1),--(A1:A10<=7),--(B1:B10="Johns"),--(C1:C10="O pen"),D1:D10) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html "Murph" wrote: I'm having trouble figuring out a way to count specific rows in a database I have, using multiple variables. For example. I have column A, B, and C. In column "A" are number values from 1-10. In Column "B" are peoples names, and in Column "C" are other text values. I want to figure out a way to count all the rows that have the numbers 1-3 in column "A", the same name in column "B", and the same text value in column "C". For example all the rows that have a value between 1 and 7 in column A, value of "Jones" in Column B, and a value of "Open" in column C. In additions to that I also need to come up with a formula that does what I described above, but in addition, subtracts and rows that have a specific value in Column "D". I've tried using multiple variations of the COUNT, DCOUNT, SUM, and DSUM functions but have had no luck. Any help would be appreciated. -- Brendan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
Find Count of Items with certain criteria | Excel Discussion (Misc queries) | |||
How do I set a Conditional Format for ROWs based on the value of . | Excel Discussion (Misc queries) | |||
Multiple rows of data on a single axis (charting) | Charts and Charting in Excel | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) |