A better way then multi sheet sumif

Joe C

Well-known Member
Joined
Oct 17, 2002
Messages
841
I have data in 2 formats,
I am using a sumif to find the where a partuicular unique match is on one sheet and then pulling the data from another identically formated sheet.

=RwPePas!G23*SUMIF(FY07Grid!$G$14:$S$578,RwGrd!G23,'FY07'!$G$14:$S$578)


So basically the
FY07Grid sheets is a breakdown of which cell is storing what data for 07.

RwGrd sheet is a breadown of which cell is storing what data for 06.

RwPePas is the percentage of the 07 data I need to pull for the cells result.

My sumif is actually always just looking up 1 unique data point. Is there a better way to write it, I wasnt sure if index or match would work because I am actually pulling number from another sheet. Also the actual match may be anywhere in the array. So trying Vlookup will not work.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
FY07Grid!$G$14:$S$578 is a multi-column range. Which column is RwGrd!G23 supposed to match and from which column a numeric value must be returned?
 

Joe C

Well-known Member
Joined
Oct 17, 2002
Messages
841
That is not know the way the 2 datasets vary the unique match I need for 07 data may be anywhere in the array. The Sumif works but it takkes a long time.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Joe C said:
That is not know the way the 2 datasets vary the unique match I need for 07 data may be anywhere in the array. The Sumif works but it takkes a long time.

I think the match range must be every Nth column. Given such a layout and no clue in which column to look, a SumIf formula is the best choice.
 

Joe C

Well-known Member
Joined
Oct 17, 2002
Messages
841
Thanks for the help.
I thought that might be the answer, but was hoping there was something less time consuming.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,652
Members
412,335
Latest member
cinciri99
Top