If and then statement to sum corresponding prices.

[Doo0592]

Hi all,

I am trying to set up a worksheet that will calculate up how much
money I'm spending on people for thier christmas and birthdays this
year (unfortunately they all come at once!). The english version of
the formula I'm am trying to write is

IF B3:B32 = "David" AND D332= "Birthday" THEN K4 = sum of
corresponding price in F3:F32.

I have to admit I'm not very good with If and then statements but I
understand the basics of programming.

 

[Doo0592]

I should clarify that again me thinks after reading it back. If any of
the cells in B3:B32 = "David" and any of the cells in C4:C32
="Birthday" then I would like to add the price in the rows that are
true. I have also noticed that I should make it iterate through the
column instead of using the range and this will check the column all
at once, I think...

:S

 

[Bernard Liengme]

IF is not the one to use. Use:
SUMPRODUCT(--(B3:B32="David"),--(D332="Birthday"),F3,F32)
For more details see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[JW]

Ctrl+Shift+Enter
=SUM(IF(B3:B32="David",IF(C3:C32="Birthday",F3:F32)))

 

[JW]

Bernard, sumproduct would certainly be more effecient than the array
formula I posted. I'm assuming that you meant to have a colon between
F3 and F32 instead of a comma.
=SUMPRODUCT(--(B3:B32="David"),--(C3:C32="Christmas"),F3:F32)

Regards,
-Jeff-

 

[Doo0592]

Thanks guys, that worked but I found you only need one - before the
ranges to allow excel to work with the strings else it will return
£0.00 always. Was thinking about ignoring the returned zero values as
well but I can't remember how..

So far I have:

=if(k4=0,"") then sumproduct formula but I can't figure out what goes
inbetween as the "else". I thought it was a comma but it won't accept
it.

 

[Doo0592]

Thanks guys, that worked but I found you only need one - before the
ranges to allow excel to work with the strings else it will return
£0.00 always. Was thinking about ignoring the returned zero values as
well but I can't remember how..

So far I have:

=if(k4=0,"") then sumproduct formula but I can't figure out what goes
inbetween as the "else". I thought it was a comma but it won't accept
it.

NM! I found a handy way to turn off the zero values in the options
menu under view.
Thanks again!