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!!

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!!

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!!

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!!

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!!

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!!