SUMPRODUCT

omypet

Board Regular
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,

Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Jon von der Heyden

MrExcel MVP, Moderator
Shouldn't it be:

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

Regards,
Jon

omypet

Board Regular
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

Well-known Member
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

Board Regular

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

omypet

Board Regular
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

Board Regular
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))

Replies
8
Views
262
Replies
0
Views
309
Replies
10
Views
652
Replies
3
Views
282
Replies
5
Views
147

1,136,994
Messages
5,679,030
Members
419,800
Latest member
spvsr999

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back