Sumproduct - sort of
 

Can anybody help with a formula please
Data is in columns & I need to apply an inflation % to a value IF ColA=Yes &
add up the values of each
y/n bci end
n 701
n 727
y 766
y 801
y 931
inflate 5%
if(b2=y,c2*5%)+(if(b3=y,c3*5%)€¦etc

So from above (766*5%)+(801*5%)+(931*5%) = 124.90
The column can grow & the 5% would need to be referenced

Thanks in advance for any help

Sumproduct - sort of
 

HI

One way
=SUMPRODUCT((A1:A100="y")*B1:B100)*5%

--
Regards

Roger Govier


"Saintsman" wrote in message
...
Can anybody help with a formula please
Data is in columns & I need to apply an inflation % to a value IF
ColA=Yes &
add up the values of each
y/n bci end
n 701
n 727
y 766
y 801
y 931
inflate 5%
if(b2=y,c2*5%)+(if(b3=y,c3*5%).etc

So from above (766*5%)+(801*5%)+(931*5%) = 124.90
The column can grow & the 5% would need to be referenced

Thanks in advance for any help

Sumproduct - sort of
 

Try this:

=SUMIF(A2:A6,"y",B2)*5%


"Saintsman" wrote:

Can anybody help with a formula please
Data is in columns & I need to apply an inflation % to a value IF ColA=Yes &
add up the values of each
y/n bci end
n 701
n 727
y 766
y 801
y 931
inflate 5%
if(b2=y,c2*5%)+(if(b3=y,c3*5%)€¦etc

So from above (766*5%)+(801*5%)+(931*5%) = 124.90
The column can grow & the 5% would need to be referenced

Thanks in advance for any help

Sumproduct - sort of
 

Thanks to you both for this very quick reply!
Rogers solution solved it for me - albeit I needed to add -- before
(A1:A100)...etc
Thanks very much

"Roger Govier" wrote:

HI

One way
=SUMPRODUCT((A1:A100="y")*B1:B100)*5%

--
Regards

Roger Govier


"Saintsman" wrote in message
...
Can anybody help with a formula please
Data is in columns & I need to apply an inflation % to a value IF
ColA=Yes &
add up the values of each
y/n bci end
n 701
n 727
y 766
y 801
y 931
inflate 5%
if(b2=y,c2*5%)+(if(b3=y,c3*5%).etc

So from above (766*5%)+(801*5%)+(931*5%) = 124.90
The column can grow & the 5% would need to be referenced

Thanks in advance for any help

Sumproduct - sort of
 

I'd try it again without the --.

The multiplication should be sufficient.

Saintsman wrote:

Thanks to you both for this very quick reply!
Rogers solution solved it for me - albeit I needed to add -- before
(A1:A100)...etc
Thanks very much

"Roger Govier" wrote:

HI

One way
=SUMPRODUCT((A1:A100="y")*B1:B100)*5%

--
Regards

Roger Govier


"Saintsman" wrote in message
...
Can anybody help with a formula please
Data is in columns & I need to apply an inflation % to a value IF
ColA=Yes &
add up the values of each
y/n bci end
n 701
n 727
y 766
y 801
y 931
inflate 5%
if(b2=y,c2*5%)+(if(b3=y,c3*5%).etc

So from above (766*5%)+(801*5%)+(931*5%) = 124.90
The column can grow & the 5% would need to be referenced

Thanks in advance for any help




--

Dave Peterson

Sumproduct - sort of
 

Doesn't need it, as the * will multiply all of the TRUEs by the equivalent
values in B1:B100 and return numeric values.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Saintsman" wrote in message
...
Thanks to you both for this very quick reply!
Rogers solution solved it for me - albeit I needed to add -- before
(A1:A100)...etc
Thanks very much

"Roger Govier" wrote:

HI

One way
=SUMPRODUCT((A1:A100="y")*B1:B100)*5%

--
Regards

Roger Govier


"Saintsman" wrote in message
...
Can anybody help with a formula please
Data is in columns & I need to apply an inflation % to a value IF
ColA=Yes &
add up the values of each
y/n bci end
n 701
n 727
y 766
y 801
y 931
inflate 5%
if(b2=y,c2*5%)+(if(b3=y,c3*5%).etc

So from above (766*5%)+(801*5%)+(931*5%) = 124.90
The column can grow & the 5% would need to be referenced

Thanks in advance for any help