Sumproduct repost

[Sandy]

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

 

[Jarek Kujawa]

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

 

[xlm]

try this

=SUMPRODUCT(--(B2:B50=L2),(F2:F50))

HTH

--
If this posting was helpful, please click on the Yes button

Thank You

cheers,

 

[Mike H]

Hi,

Try this and drag down

=SUMPRODUCT(($B$2:$B$50=L2)*($F$2:$F$50))

Mike

 

[Sandy]

Hi Jarek
I'm afraid this just produces all 0's as well
Sandy

 

[Sandy]

Hi MikeH
I'm afraid this just produces all 0's as well
Sandy

 

[Sandy]

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

 

[Sandy]

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

 

[google]

Then

=SUMPRODUCT(--($B$2:$B$50=L2),($F$2:$F$50))

and Edit->Fill->Down

'nuff said.