count rows that meet two criteria in two different columns?
I need to count the number of times a person, who's name is in column A, has
a specific value,"0", in column B, where the worksheet contains an ever-increasing amount of records (rows). Is there an easy way to make this happen? Any help is appreciated. Thanks |
count rows that meet two criteria in two different columns?
Look at Jon Peltier's page on dynamic charts
http://peltiertech.com/Excel/Charts/index.html#hdrDyno What you need is the information on creating dynamic named ranges, which will expand or contract according to the size of your data. It looks a little daunting at first, but you'll quickly get used to it. Then use the range names in your calculations. "dsk3808" wrote: I need to count the number of times a person, who's name is in column A, has a specific value,"0", in column B, where the worksheet contains an ever-increasing amount of records (rows). Is there an easy way to make this happen? Any help is appreciated. Thanks |
count rows that meet two criteria in two different columns?
Something along these lines should suffice:
In C1: =SUMPRODUCT((A1:A20="Name1")*(B1:B20=0)) Use the smallest range sufficient to cover the max expected extent of data in cols A and B -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dsk3808" wrote: I need to count the number of times a person, who's name is in column A, has a specific value,"0", in column B, where the worksheet contains an ever-increasing amount of records (rows). Is there an easy way to make this happen? Any help is appreciated. Thanks |
count rows that meet two criteria in two different columns?
Thanks for the response guys, but I'm not sure that either one does the job
I'm looking to do. Basically, I need to use the SUMIF function with two criteria. Here is an example of my current SUMIF functions: =SUMIF('Data Record'!C:C,"Name",'Data Record'!I:I) Which means that for every row in column C that contains "NAME" add in the corresponding cell from column I. What I need to do is expand that by one criteria. I need the formula to say: For every row that contains "NAME" in column C and contains "0" in column J, add 1. Basically, I need a count function based on two criteria. 1) column contains a specific name AND 2) column J contains the number "0" Hopefully this helps? "Max" wrote: Something along these lines should suffice: In C1: =SUMPRODUCT((A1:A20="Name1")*(B1:B20=0)) Use the smallest range sufficient to cover the max expected extent of data in cols A and B -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dsk3808" wrote: I need to count the number of times a person, who's name is in column A, has a specific value,"0", in column B, where the worksheet contains an ever-increasing amount of records (rows). Is there an easy way to make this happen? Any help is appreciated. Thanks |
count rows that meet two criteria in two different columns?
=SUMIF('Data Record'!C:C,"Name",'Data Record'!I:I)
I need the formula to say: For every row that contains "NAME" in column C and contains "0" in column J, add 1. In C1: =SUMPRODUCT((A1:A20="Name1")*(B1:B20=0)) The earlier SUMPRODUCT should have worked (when adapted to suit your layout), but we can't use entire col refs in SUMPRODUCT, unlike SUMIF. I should have mentioned this in the earlier response, sorry. Try something like this: =SUMPRODUCT(('Data Record'!C2:C1000="Name")*('Data Record'!I2:I1000=0)) Adapt the ranges to suit. The ranges involved should be identical in size. As stated earlier, use the smallest range sufficient to cover the max expected extent of data in cols C and I. This is to keep performance optimal / tolerable* (calc speed). *albeit this would be accepted, don't use eg: 'Data Record'!C2:C65536 when data is expected only up to, say row 2000 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dsk3808" wrote: Thanks for the response guys, but I'm not sure that either one does the job I'm looking to do. Basically, I need to use the SUMIF function with two criteria. Here is an example of my current SUMIF functions: =SUMIF('Data Record'!C:C,"Name",'Data Record'!I:I) Which means that for every row in column C that contains "NAME" add in the corresponding cell from column I. What I need to do is expand that by one criteria. I need the formula to say: For every row that contains "NAME" in column C and contains "0" in column J, add 1. Basically, I need a count function based on two criteria. 1) column contains a specific name AND 2) column J contains the number "0" |
count rows that meet two criteria in two different columns?
Set up tow named ranges, one for column C (called "Name" or something you can
remember) and one for column I ("Value") outlined on Peltier's page. Then enter this formula =SUM(IF(Name="Bob",Value,0)) hold down the Ctrl and Shift keys when you press Enter. Your formula be enclosed in brackets in the editing window {=SUM(IF(Name="Bob",Value,0))} In this case, every time the name in the range "Name" = "Bob" (Column C) it will add the corresponding value in column I HTH JonR "dsk3808" wrote: Thanks for the response guys, but I'm not sure that either one does the job I'm looking to do. Basically, I need to use the SUMIF function with two criteria. Here is an example of my current SUMIF functions: =SUMIF('Data Record'!C:C,"Name",'Data Record'!I:I) Which means that for every row in column C that contains "NAME" add in the corresponding cell from column I. What I need to do is expand that by one criteria. I need the formula to say: For every row that contains "NAME" in column C and contains "0" in column J, add 1. Basically, I need a count function based on two criteria. 1) column contains a specific name AND 2) column J contains the number "0" Hopefully this helps? "Max" wrote: Something along these lines should suffice: In C1: =SUMPRODUCT((A1:A20="Name1")*(B1:B20=0)) Use the smallest range sufficient to cover the max expected extent of data in cols A and B -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dsk3808" wrote: I need to count the number of times a person, who's name is in column A, has a specific value,"0", in column B, where the worksheet contains an ever-increasing amount of records (rows). Is there an easy way to make this happen? Any help is appreciated. Thanks |
count rows that meet two criteria in two different columns?
Sorry, should have read the whole post.
Set up another name for Column J (call it "qualifier") You can nest your IF statements thus: =SUM(IF(Name="Bob",IF(Qualifier = 0,1,0),0) Here I substituted "1" for the "Value" in the conditional sum statement, since you are basically only adding 1 for each time all criteria are met, but the concept is the same. Remember to hold down Ctrl-Shift when you press Enter "JonR" wrote: Set up tow named ranges, one for column C (called "Name" or something you can remember) and one for column I ("Value") outlined on Peltier's page. Then enter this formula =SUM(IF(Name="Bob",Value,0)) hold down the Ctrl and Shift keys when you press Enter. Your formula be enclosed in brackets in the editing window {=SUM(IF(Name="Bob",Value,0))} In this case, every time the name in the range "Name" = "Bob" (Column C) it will add the corresponding value in column I HTH JonR "dsk3808" wrote: Thanks for the response guys, but I'm not sure that either one does the job I'm looking to do. Basically, I need to use the SUMIF function with two criteria. Here is an example of my current SUMIF functions: =SUMIF('Data Record'!C:C,"Name",'Data Record'!I:I) Which means that for every row in column C that contains "NAME" add in the corresponding cell from column I. What I need to do is expand that by one criteria. I need the formula to say: For every row that contains "NAME" in column C and contains "0" in column J, add 1. Basically, I need a count function based on two criteria. 1) column contains a specific name AND 2) column J contains the number "0" Hopefully this helps? "Max" wrote: Something along these lines should suffice: In C1: =SUMPRODUCT((A1:A20="Name1")*(B1:B20=0)) Use the smallest range sufficient to cover the max expected extent of data in cols A and B -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dsk3808" wrote: I need to count the number of times a person, who's name is in column A, has a specific value,"0", in column B, where the worksheet contains an ever-increasing amount of records (rows). Is there an easy way to make this happen? Any help is appreciated. Thanks |
All times are GMT +1. The time now is 01:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com