Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct repost
Hi
Last post seems to have disappeared so I'll try again. In B2:B50 I have dates in the format 27/11/2008. The last date being 31/12/08 In F2:F50 I have £ totals. There are more than one instance of many dates. In L2:L36 I have consecutive dates from 27/11/2008 to 31/12/08 I am using the following to acquire the totals for each day but it is failing, it returns 0 for all entries. =SUMPRODUCT(--($B$2:$B$50=L2),--($F$2:$F$50)) Help appreciated Sandy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct repost
i'd try
=SUM(IF($B$2:$B$50=L2,$F$2:$F$50,)) CTRL+SHIFT+ENTER this formula as this ia an array-formula copy down On 11 Gru, 20:31, "Sandy" wrote: Hi Last post seems to have disappeared so I'll try again. In B2:B50 I have dates in the format 27/11/2008. The last date being 31/12/08 In F2:F50 I have £ totals. There are more than one instance of many dates. In L2:L36 I have consecutive dates from 27/11/2008 to 31/12/08 I am using the following to acquire the totals for each day but it is failing, it returns 0 for all entries. =SUMPRODUCT(--($B$2:$B$50=L2),--($F$2:$F$50)) Help appreciated Sandy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct repost
try this
=SUMPRODUCT(--(B2:B50=L2),(F2:F50)) HTH -- If this posting was helpful, please click on the Yes button Thank You cheers, "Sandy" wrote: Hi Last post seems to have disappeared so I'll try again. In B2:B50 I have dates in the format 27/11/2008. The last date being 31/12/08 In F2:F50 I have £ totals. There are more than one instance of many dates. In L2:L36 I have consecutive dates from 27/11/2008 to 31/12/08 I am using the following to acquire the totals for each day but it is failing, it returns 0 for all entries. =SUMPRODUCT(--($B$2:$B$50=L2),--($F$2:$F$50)) Help appreciated Sandy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct repost
Hi,
Try this and drag down =SUMPRODUCT(($B$2:$B$50=L2)*($F$2:$F$50)) Mike "Sandy" wrote: Hi Last post seems to have disappeared so I'll try again. In B2:B50 I have dates in the format 27/11/2008. The last date being 31/12/08 In F2:F50 I have £ totals. There are more than one instance of many dates. In L2:L36 I have consecutive dates from 27/11/2008 to 31/12/08 I am using the following to acquire the totals for each day but it is failing, it returns 0 for all entries. =SUMPRODUCT(--($B$2:$B$50=L2),--($F$2:$F$50)) Help appreciated Sandy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct repost
Hi Jarek
I'm afraid this just produces all 0's as well Sandy "Jarek Kujawa" wrote in message ... i'd try =SUM(IF($B$2:$B$50=L2,$F$2:$F$50,)) CTRL+SHIFT+ENTER this formula as this ia an array-formula copy down On 11 Gru, 20:31, "Sandy" wrote: Hi Last post seems to have disappeared so I'll try again. In B2:B50 I have dates in the format 27/11/2008. The last date being 31/12/08 In F2:F50 I have £ totals. There are more than one instance of many dates. In L2:L36 I have consecutive dates from 27/11/2008 to 31/12/08 I am using the following to acquire the totals for each day but it is failing, it returns 0 for all entries. =SUMPRODUCT(--($B$2:$B$50=L2),--($F$2:$F$50)) Help appreciated Sandy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct repost
Hi MikeH
I'm afraid this just produces all 0's as well Sandy "Mike H" wrote in message ... Hi, Try this and drag down =SUMPRODUCT(($B$2:$B$50=L2)*($F$2:$F$50)) Mike "Sandy" wrote: Hi Last post seems to have disappeared so I'll try again. In B2:B50 I have dates in the format 27/11/2008. The last date being 31/12/08 In F2:F50 I have £ totals. There are more than one instance of many dates. In L2:L36 I have consecutive dates from 27/11/2008 to 31/12/08 I am using the following to acquire the totals for each day but it is failing, it returns 0 for all entries. =SUMPRODUCT(--($B$2:$B$50=L2),--($F$2:$F$50)) Help appreciated Sandy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct repost
Hi xlm
This one works, but the problem is because the cell references are not absolute the range keeps changing as it is dragged down. Sandy "xlm" wrote in message ... try this =SUMPRODUCT(--(B2:B50=L2),(F2:F50)) HTH -- If this posting was helpful, please click on the Yes button Thank You cheers, "Sandy" wrote: Hi Last post seems to have disappeared so I'll try again. In B2:B50 I have dates in the format 27/11/2008. The last date being 31/12/08 In F2:F50 I have £ totals. There are more than one instance of many dates. In L2:L36 I have consecutive dates from 27/11/2008 to 31/12/08 I am using the following to acquire the totals for each day but it is failing, it returns 0 for all entries. =SUMPRODUCT(--($B$2:$B$50=L2),--($F$2:$F$50)) Help appreciated Sandy |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct repost
Solved!
The problem was a rogue formula in column F. After correcting that all solutions including my original work as they should. Thank you all for your efforts and apologies for having an error that caused so much trouble. Sandy "Sandy" wrote in message ... Hi Last post seems to have disappeared so I'll try again. In B2:B50 I have dates in the format 27/11/2008. The last date being 31/12/08 In F2:F50 I have £ totals. There are more than one instance of many dates. In L2:L36 I have consecutive dates from 27/11/2008 to 31/12/08 I am using the following to acquire the totals for each day but it is failing, it returns 0 for all entries. =SUMPRODUCT(--($B$2:$B$50=L2),--($F$2:$F$50)) Help appreciated Sandy |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct repost
Then
=SUMPRODUCT(--($B$2:$B$50=L2),($F$2:$F$50)) and Edit-Fill-Down 'nuff said. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repost for Ron | Excel Discussion (Misc queries) | |||
Drop Down - Repost | Excel Discussion (Misc queries) | |||
REPOST: Which Function to Use | Excel Worksheet Functions | |||
repost: Min/Max help needed with a SumProduct | Excel Worksheet Functions | |||
Repost of Using IF and SUMPRODUCT | Excel Worksheet Functions |