Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If and then statement to sum corresponding prices.
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 D3:D32= "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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If and then statement to sum corresponding prices.
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If and then statement to sum corresponding prices.
IF is not the one to use. Use:
SUMPRODUCT(--(B3:B32="David"),--(D3:D32="Birthday"),F3,F32) For more details see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Doo0592" wrote in message oups.com... 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 D3:D32= "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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If and then statement to sum corresponding prices.
Ctrl+Shift+Enter
=SUM(IF(B3:B32="David",IF(C3:C32="Birthday",F3:F32 ))) Doo0592 wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If and then statement to sum corresponding prices.
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- Bernard Liengme wrote: IF is not the one to use. Use: SUMPRODUCT(--(B3:B32="David"),--(D3:D32="Birthday"),F3,F32) For more details see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Doo0592" wrote in message oups.com... 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 D3:D32= "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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If and then statement to sum corresponding prices.
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If and then statement to sum corresponding prices.
On 11 Oct, 15:06, Doo0592 wrote:
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I round prices to end in .95? | Excel Worksheet Functions | |||
Looking up prices | Excel Worksheet Functions | |||
prices | Excel Discussion (Misc queries) | |||
Look up a list of prices | Excel Worksheet Functions | |||
Use a multiplier to change List Prices to Net prices | Excel Worksheet Functions |