Can anyone help???

D

Daniellel

Board Regular
Joined
Jun 21, 2011
Messages
242
How can i get excel to look for a changing range within a formula???

This is the current formula i am using... The bit i need to change is ****** ...
=SUMPRODUCT(--('Holiday and Sickness Tracker 11'!*********=$A7),('Holiday and Sickness Tracker 11'!$AN$1:$AN$8977))

The formula i would use to find the start and end row numbers is a Match...

Starting Row =MATCH(I7,'Holiday and Sickness Tracker 11'!$A$1:$A$9916,0)
Ending Row =MATCH(End Of Year,'Holiday and Sickness Tracker 11'!$A$1:$A$9916,0)

How can i get this to work all in one sum of am i approaching it all wrong???

PLEASE HELP!!!

I am currently using Excel 2000 if that helps....
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Hi Danielle,
For SUMPRODUCT to work all ranges must be the same lenght whereas in your case formula you need would return a range of a diffrent lenght.
Try this
(Array formula confirm CTRL+SHIFT+ENTER):
=SUMPRODUCT(--(INDEX($A$1:$A$10000,MATCH(I7,$A$1:$A$10000,0)):INDEX($A$1:$A$10000,MATCH(EndOfYear,$A$1:$A$10000,0))=$A7),(INDEX($AN$1:$AN$10000,MATCH(I7,$A$1:$A$10000,0)):INDEX($AN$1:$AN$10000,MATCH(EndOfYear,$A$1:$A$10000,0))))

This will return always range of the same lenght where I7 is the start of the range and EndOfYear is the named cell to point to the end of the range.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Dynamic Sheet Tabs in Index Match function
Replies
9
Views
273
Peter_SSs
Peter_SSs
A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
245
Peter_SSs
Peter_SSs
R
  • Question Question
Graphic Analysis of my Sick Leave Tracker
Replies
0
Views
846
RBLuckett01
R
S
  • Question Question
Adding a key (So if someone clicks "P" in the excel, it will automatically turn that cell Purple with the letter P in it)
Replies
6
Views
2K
sheri54371
S
I
  • Question Question
Matching Multiple Criteria to Extract a Date
Replies
4
Views
126
hagia_sofia
hagia_sofia

Forum statistics

Threads
1,224,599
Messages
6,179,827
Members
452,946
Latest member
JoseDavid

Share this page

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
Back
Top