Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
SumIF using two criteria
I have a sumif question. I have several columns of data. I want to sum one
column based on another column's criteria but only to want to sum the data of the first column if it is above a certain score. This is formula i am using so far =SUMIF(F4:F253,"207",D4:D253). I am trying to sum range 'F' if it is greater than 207 but only if the corresponding number in range 'D' is 3. I have tried using several different methods but this one gets me the closest. I am trying to simplify my equations alread in place so i do not have to reenter the row numbers each time. Any help would be greatly appreciated. Thanks!! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
SumIF using two criteria
Hi
Try =SUMPRODUCT(($F4:$F253207)*($D$4:$D$253=3)*$D$4:$ D$253) -- Regards Roger Govier "moe9414" wrote in message ... I have a sumif question. I have several columns of data. I want to sum one column based on another column's criteria but only to want to sum the data of the first column if it is above a certain score. This is formula i am using so far =SUMIF(F4:F253,"207",D4:D253). I am trying to sum range 'F' if it is greater than 207 but only if the corresponding number in range 'D' is 3. I have tried using several different methods but this one gets me the closest. I am trying to simplify my equations alread in place so i do not have to reenter the row numbers each time. Any help would be greatly appreciated. Thanks!! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
SumIF using two criteria
Try this:
=SUMPRODUCT(--(D4:D253=3),--(F4:F253207),F4:F253) -- Biff Microsoft Excel MVP "moe9414" wrote in message ... I have a sumif question. I have several columns of data. I want to sum one column based on another column's criteria but only to want to sum the data of the first column if it is above a certain score. This is formula i am using so far =SUMIF(F4:F253,"207",D4:D253). I am trying to sum range 'F' if it is greater than 207 but only if the corresponding number in range 'D' is 3. I have tried using several different methods but this one gets me the closest. I am trying to simplify my equations alread in place so i do not have to reenter the row numbers each time. Any help would be greatly appreciated. Thanks!! |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
SumIF using two criteria
Thanks Valko! - This one worked. Two more questions for you however, first
what does the '--' in the formula mean/do? And secondly I have the sum, now I have to do a percentage from this. How do I/do I use the countif expression to find the total number of scores above 207 rather than the total of the scores this time? (i.e. there were 25 scores above 207 if the first column was 3, just like below but instead of the sum i need the count) I have the second part (denominator) of this part of particular equation finished but I need the numerator. Thanks for the support!! "T. Valko" wrote: Try this: =SUMPRODUCT(--(D4:D253=3),--(F4:F253207),F4:F253) -- Biff Microsoft Excel MVP "moe9414" wrote in message ... I have a sumif question. I have several columns of data. I want to sum one column based on another column's criteria but only to want to sum the data of the first column if it is above a certain score. This is formula i am using so far =SUMIF(F4:F253,"207",D4:D253). I am trying to sum range 'F' if it is greater than 207 but only if the corresponding number in range 'D' is 3. I have tried using several different methods but this one gets me the closest. I am trying to simplify my equations alread in place so i do not have to reenter the row numbers each time. Any help would be greatly appreciated. Thanks!! |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
SumIF using two criteria
Again, thanks Valko,
I believe i have figured it out. I used the formula below but instead of adding the third array, i simply removed it and was left with just the count. i then divided by the number of '3's and was able to get my percentage. This is wonderful knowledge to take with me into the future! "T. Valko" wrote: Try this: =SUMPRODUCT(--(D4:D253=3),--(F4:F253207),F4:F253) -- Biff Microsoft Excel MVP "moe9414" wrote in message ... I have a sumif question. I have several columns of data. I want to sum one column based on another column's criteria but only to want to sum the data of the first column if it is above a certain score. This is formula i am using so far =SUMIF(F4:F253,"207",D4:D253). I am trying to sum range 'F' if it is greater than 207 but only if the corresponding number in range 'D' is 3. I have tried using several different methods but this one gets me the closest. I am trying to simplify my equations alread in place so i do not have to reenter the row numbers each time. Any help would be greatly appreciated. Thanks!! |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
SumIF using two criteria
I believe i have figured it out.
instead of adding the third array, i simply removed it and was left with just the count. Yep. That's how you do it. See this for an explanation of the "--" : http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "moe9414" wrote in message ... Again, thanks Valko, I believe i have figured it out. I used the formula below but instead of adding the third array, i simply removed it and was left with just the count. i then divided by the number of '3's and was able to get my percentage. This is wonderful knowledge to take with me into the future! "T. Valko" wrote: Try this: =SUMPRODUCT(--(D4:D253=3),--(F4:F253207),F4:F253) -- Biff Microsoft Excel MVP "moe9414" wrote in message ... I have a sumif question. I have several columns of data. I want to sum one column based on another column's criteria but only to want to sum the data of the first column if it is above a certain score. This is formula i am using so far =SUMIF(F4:F253,"207",D4:D253). I am trying to sum range 'F' if it is greater than 207 but only if the corresponding number in range 'D' is 3. I have tried using several different methods but this one gets me the closest. I am trying to simplify my equations alread in place so i do not have to reenter the row numbers each time. Any help would be greatly appreciated. Thanks!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumIf with OR criteria | Excel Worksheet Functions | |||
sumif with more than 1 criteria | Excel Worksheet Functions | |||
SUMIF Criteria | Excel Worksheet Functions | |||
sumif with criteria | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions |