ExcelBanter - using sumif & countif to sort multiple cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - using sumif & countif to sort multiple cells (https://www.excelbanter.com/excel-worksheet-functions/14750-using-sumif-countif-sort-multiple-cells.html)

floridasurfn

using sumif & countif to sort multiple cells

i have this information:

A1= YES B1=YES C1=10
A2= YES B2=NO C2=15
A3= NO B3=NO C3=25
A4=YES B4=NO C4=6
A5=YES B5=YES C5=11

I would like to sumif, range a1:a5 =yes & range b1:b5 =yes, then sum range
c1:c5
Can a formula check ranges and return (21) which is sum of c1 & c5

Ken Wright

=SUMPRODUCT(--(A1:A5="YES"),--(B1:B5="YES"),C1:C5)

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"floridasurfn" wrote in message
...
i have this information:

A1= YES B1=YES C1=10
A2= YES B2=NO C2=15
A3= NO B3=NO C3=25
A4=YES B4=NO C4=6
A5=YES B5=YES C5=11

I would like to sumif, range a1:a5 =yes & range b1:b5 =yes, then sum range
c1:c5
Can a formula check ranges and return (21) which is sum of c1 & c5

floridasurfn

thanks ken this worked perfect

"floridasurfn" wrote:

i have this information:

A1= YES B1=YES C1=10
A2= YES B2=NO C2=15
A3= NO B3=NO C3=25
A4=YES B4=NO C4=6
A5=YES B5=YES C5=11

I would like to sumif, range a1:a5 =yes & range b1:b5 =yes, then sum range
c1:c5
Can a formula check ranges and return (21) which is sum of c1 & c5

Ken Wright

You're welcome :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"floridasurfn" wrote in message
...
thanks ken this worked perfect

"floridasurfn" wrote:

i have this information:

A1= YES B1=YES C1=10
A2= YES B2=NO C2=15
A3= NO B3=NO C3=25
A4=YES B4=NO C4=6
A5=YES B5=YES C5=11

I would like to sumif, range a1:a5 =yes & range b1:b5 =yes, then sum
range
c1:c5
Can a formula check ranges and return (21) which is sum of c1 & c5

All times are GMT +1. The time now is 05:15 AM.