SUMPRODUCT

omypet

Board Regular
Joined
Oct 8, 2005
Messages
71
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
 

Excel Facts

Add Bullets to Range
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
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Shouldn't it be:

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

Regards,
Jon
 

omypet

Board Regular
Joined
Oct 8, 2005
Messages
71
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
Joined
Jul 8, 2002
Messages
11,547
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
Joined
Oct 8, 2005
Messages
71

ADVERTISEMENT

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
Joined
Oct 8, 2005
Messages
71
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
Joined
Oct 8, 2005
Messages
71
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))
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top