Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Sumif function
I am using the sumif function to summarize data from one worksheet on another
worksheet in the same workbook. The worksheet I am referencing contains a pivot table which automatically refreshes each time I open the workbook. My formula looks like this SUMIF(Hours!A10:A75,"12/31/07",Hours!U10:U75). It works fine and returns the right number. My problem is that because the Hours worksheet contains pivot table data, when it is refreshed the column I need to have summed might be "AA" instead of "U". Is there anyway to direct Excel to have the column reference in my formula float with the column heading "Total Amount"? Or is there some other formula I should be using? I hope that makes sense. Thanks, Dawn |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Sumif function
direct Excel to have the column reference in my formula
float with the column heading "Total Amount"? OFFSET is one way to get it done Assuming the pivot headers are in row 9 this expression should grab it for you: =SUMIF(Hours!A10:A75,"12/31/07",OFFSET(Hours!A10:A75,,MATCH("Total Amount",Hours!$9:$9,0)-1)) Adapt to suit -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dawn" wrote: I am using the sumif function to summarize data from one worksheet on another worksheet in the same workbook. The worksheet I am referencing contains a pivot table which automatically refreshes each time I open the workbook. My formula looks like this SUMIF(Hours!A10:A75,"12/31/07",Hours!U10:U75). It works fine and returns the right number. My problem is that because the Hours worksheet contains pivot table data, when it is refreshed the column I need to have summed might be "AA" instead of "U". Is there anyway to direct Excel to have the column reference in my formula float with the column heading "Total Amount"? Or is there some other formula I should be using? I hope that makes sense. Thanks, Dawn |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Sumif function
Untested...
Should this: =SUMIF(Hours!A10:A75,"12/31/07", ... be more like: =SUMIF(Hours!A10:A75,""&date(2007,12,31), .... Max wrote: direct Excel to have the column reference in my formula float with the column heading "Total Amount"? OFFSET is one way to get it done Assuming the pivot headers are in row 9 this expression should grab it for you: =SUMIF(Hours!A10:A75,"12/31/07",OFFSET(Hours!A10:A75,,MATCH("Total Amount",Hours!$9:$9,0)-1)) Adapt to suit -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dawn" wrote: I am using the sumif function to summarize data from one worksheet on another worksheet in the same workbook. The worksheet I am referencing contains a pivot table which automatically refreshes each time I open the workbook. My formula looks like this SUMIF(Hours!A10:A75,"12/31/07",Hours!U10:U75). It works fine and returns the right number. My problem is that because the Hours worksheet contains pivot table data, when it is refreshed the column I need to have summed might be "AA" instead of "U". Is there anyway to direct Excel to have the column reference in my formula float with the column heading "Total Amount"? Or is there some other formula I should be using? I hope that makes sense. Thanks, Dawn -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Sumif function
Dave,
Curiously the original version worked (as the OP mentioned in the post) when I plugged it in here, so I didn't pay that part of it further attention since it wasn't the focus. But I agree that your version -- which works, too -- is the better, unambiguous way to go. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dave Peterson" wrote in message ... Untested... Should this: =SUMIF(Hours!A10:A75,"12/31/07", ... be more like: =SUMIF(Hours!A10:A75,""&date(2007,12,31), .... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Sumif function
I would look carefully and test thoroughly, if I were you, as the formulae
do not give identical results. -- David Biddulph "Max" wrote in message ... Dave, Curiously the original version worked (as the OP mentioned in the post) when I plugged it in here, so I didn't pay that part of it further attention since it wasn't the focus. But I agree that your version -- which works, too -- is the better, unambiguous way to go. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dave Peterson" wrote in message ... Untested... Should this: =SUMIF(Hours!A10:A75,"12/31/07", ... be more like: =SUMIF(Hours!A10:A75,""&date(2007,12,31), .... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Sumif function
I would look carefully and test thoroughly, if I were you, as the formulae
do not give identical results. well, it does return identical (Dave's & the OP's version) in my tests here. perhaps you could help by stating which source dates won't work ? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Sumif function
THANKS! You guys rock! That seems to be working for me!
"Max" wrote: direct Excel to have the column reference in my formula float with the column heading "Total Amount"? OFFSET is one way to get it done Assuming the pivot headers are in row 9 this expression should grab it for you: =SUMIF(Hours!A10:A75,"12/31/07",OFFSET(Hours!A10:A75,,MATCH("Total Amount",Hours!$9:$9,0)-1)) Adapt to suit -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dawn" wrote: I am using the sumif function to summarize data from one worksheet on another worksheet in the same workbook. The worksheet I am referencing contains a pivot table which automatically refreshes each time I open the workbook. My formula looks like this SUMIF(Hours!A10:A75,"12/31/07",Hours!U10:U75). It works fine and returns the right number. My problem is that because the Hours worksheet contains pivot table data, when it is refreshed the column I need to have summed might be "AA" instead of "U". Is there anyway to direct Excel to have the column reference in my formula float with the column heading "Total Amount"? Or is there some other formula I should be using? I hope that makes sense. Thanks, Dawn |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Sumif function
Apologies for the confusion.
I now realise that the reason that I was getting different answers (zero in the case of the first formula) is that we speak different languages. If I change your formula from ...,"12/31/07",... to ...,"31/12/07",... then I get the same correct answer as using ...,date(2007,12,31),... I should have realised, as you obviously had when you mentioned that the latter was "the better, unambiguous way to go." I knew that there was cases where 12/31/07 is treated as 12 divided by 31 divided by 07, but I knew that if it were treated that way I would be getting all TRUE results from the condition, but I wasn't wide enough awake at the time to realise that it was presumably treating 12/31/07 as a string in this case, and hence the date comparison was always coming out FALSE. -- David Biddulph "Max" wrote in message ... I would look carefully and test thoroughly, if I were you, as the formulae do not give identical results. well, it does return identical (Dave's & the OP's version) in my tests here. perhaps you could help by stating which source dates won't work ? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Sumif function
welcome, Dawn. glad to hear that.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dawn" wrote in message ... THANKS! You guys rock! That seems to be working for me! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Sumif function
No prob, David. Thanks for clarifying.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Apologies for the confusion. I now realise that the reason that I was getting different answers (zero in the case of the first formula) is that we speak different languages. If I change your formula from ...,"12/31/07",... to ...,"31/12/07",... then I get the same correct answer as using ...,date(2007,12,31),... I should have realised, as you obviously had when you mentioned that the latter was "the better, unambiguous way to go." I knew that there was cases where 12/31/07 is treated as 12 divided by 31 divided by 07, but I knew that if it were treated that way I would be getting all TRUE results from the condition, but I wasn't wide enough awake at the time to realise that it was presumably treating 12/31/07 as a string in this case, and hence the date comparison was always coming out FALSE. -- David Biddulph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
Using the TODAY() function in a SUMIF function | Excel Worksheet Functions | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
Can SUMIF function include AND function | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |