SUMPRODUCT

[omypet]

The date of my worksheet is on H1 = 20 Oct 2006

In C8, I want the data from C32=12345.0 to be picked up

I have a day number in A32 = 19 and the data I want to get is on C32 = 12345.0

I used this formula

=SUMPRODUCT(($A$11:$C$32=DAY($H$1)-1)*($C$11:$C$32=DAY($H$1)-1)*($C14:$C$32))

I get #NA,

please help tks

 

[Jon von der Heyden]

Shouldn't it be:

=VLOOKUP(DAY(H1)-1,A11:C32,3,0)?

Regards,
Jon

 

[omypet]

Hi Jon, thanks for your suggestion, sorry if my statements before is not complete,
On this worksheet I usually change the date in H1, and add a new date in (eg. A33=20 and C33=12345.0)
so I'm pressume to have an standard formula in C8 without changing it daily

 

[GlennUK]

Are you trying to summarise multiple instances of the day number in column A?

Or are you just wanting to retrieve the value for a match for the day for a single cell in column A? If so, then Jon's formula is right ... and I can't see what you mean about changing the formula daily.

 

[omypet]

That's right glenn, using the vlookup and adjusting the range will do, I got an idea now, thanks for the help guys...

 

[omypet]

One more question here folks, if my worksheet date is 01 Nov 2006, day(1)-1 is equal 0, since oct (jan, mar, may, jul, aug & dec) has 31 days.

For 01 Oct 2006 worksheet this formula works.

IF(OR(DAY($H$1)=1,VLOOKUP(DAY($H$1)+29,$A$11:$N$44,7,0),VLOOKUP(DAY($H$1)-1,$A$11:$N$44,7,0))

The date of worksheet is behind by 1 day of the report so 01 Oct 2006 report is actually the whole whole date of Sept 1 to 30.

 

[omypet]

Sorry I forgot to delete the "or"
The formula should read like this

IF(DAY($H$1)=1,VLOOKUP(DAY($H$1)+29,$A$11:$N$44,7,0),VLOOKUP(DAY($H$1)-1,$A$11:$N$44,7,0))