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 OpenPending Inventory'!A2:A99999)=1),(('749 OpenPending 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"),D110) 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 110. 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 13 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 OpenPending Inventory'!A2:A9999=1),('749 OpenPending 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 OpenPending Inventory'!A2:A99999)=1),(('749 OpenPending 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"),D110) 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 110. 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 13 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) 