SUMIFS with INDIRECT and VLOOKUP nightmare!

[Bengo]

Hi,

I have a worksheet of data with criteria in several columns

I am trying to do a sumifs of one column which lies on another sheet based on mutliple criteria, but with the ability to change the range the sumifs look at depending on the periods I want to analyse

The raw data will extend downwards as the year goes on and we add to it each month, but as the number of additional rows may vary, I have looked up the start and end rows for each period to use as an INDIRECT for the SUMIFS range (e.g Period 1 to Period 4 would looke at column $AE1:$AE2305, Period 2 to Period 4 would look at $AE1221:$AE3576,...)

However I realised that SUMIFS cannot handle a range like $AE1:$AE2305, only $AE;$AE, and therefore returns a #VALUE! error

Can anyone please help with a workaround and save my hair?

Many thanks!

 

[etaf]

sumifs() can handle a range $AE1:$AE2305 and can use indirect
maybe it needs

$AE$1:$AE$2305

otherwise as you copy down it will be
$AE2:$AE2306
$AE3:$AE2307
etc

 

[Gerald Higgins]

Is PERIOD a part of your data ? If yes, you can add that to your SUMIFS formula.

 

[Bengo]

sumifs() can handle a range $AE1:$AE2305 and can use indirect
maybe it needs

$AE$1:$AE$2305

otherwise as you copy down it will be
$AE2:$AE2306
$AE3:$AE2307
etc

Thanks, maybe I should upload the actual formula, as even if I put this into one cell and am not trying to drag down, when I add row numbers it returns an error even though it returns the correct totals within the whole data without the cell numbers... Problem is I only want to look at part of the data as raw data contains data for other periods I do not want to look at

=SUMIFS('HC RAW DATA'!$AE:$AE,'HC RAW DATA'!$B:$B,'KNUPIC daypart'!C$23,'HC RAW DATA'!$D:$D,'KNUPIC daypart'!$A24,'HC RAW DATA'!$C:$C,'KNUPIC daypart'!$B$1)/$N$4 - this returns correct numbers but includes all results for the whole data range (this is what will get added to each month downwanrds)

=SUMIFS('HC RAW DATA'!$AE$1:$AE$1000,'HC RAW DATA'!$B:$B,'KNUPIC daypart'!C$23,'HC RAW DATA'!$D:$D,'KNUPIC daypart'!$A24,'HC RAW DATA'!$C:$C,'KNUPIC daypart'!$B$1)/$N$4 - this returns a #VALUE! error. The only part I changed was adding the row numbers in....

Any ideas?

Many thanks

 

[Bengo]

Is PERIOD a part of your data ? If yes, you can add that to your SUMIFS formula.

Thanks, but no

I could add another filter to pull the period back, but currently in the raw data these are rotas with start dates on. I might want to look at 3 date starts (3 rotas), or 15 date starts (15 rotas) with all the rotas in between...

So I might want to just look at the 4 rotas for "Period 1", but I might also want to look from "Period 1 week 1" to "Period 3 week 2". This wouldn't work when trying to define a dynamic range to sum within...

 

[etaf]

you cannot just change part of the range in the formula you need to change them all

 

[etaf]

=SUMIFS('HC RAW DATA'!$AE$1:$AE$1000,'HC RAW DATA'!$B:$B,'KNUPIC daypart'!C$23,'HC RAW DATA'!$D:$D,'KNUPIC daypart'!$A24,'HC RAW DATA'!$C:$C,'KNUPIC daypart'!$B$1)/$N$4

change to
=SUMIFS('HC RAW DATA'!$AE$1:$AE$1000,'HC RAW DATA'!$B$1:$B$1000,'KNUPIC daypart'!C$23,'HC RAW DATA'!$D$1:$D$1000,'KNUPIC daypart'!$A24,'HC RAW DATA'!$C$1:$C$1000,'KNUPIC daypart'!$B$1)/$N$4

 

[Bengo]

=SUMIFS('HC RAW DATA'!$AE$1:$AE$1000,'HC RAW DATA'!$B:$B,'KNUPIC daypart'!C$23,'HC RAW DATA'!$D:$D,'KNUPIC daypart'!$A24,'HC RAW DATA'!$C:$C,'KNUPIC daypart'!$B$1)/$N$4

change to
=SUMIFS('HC RAW DATA'!$AE$1:$AE$1000,'HC RAW DATA'!$B$1:$B$1000,'KNUPIC daypart'!C$23,'HC RAW DATA'!$D$1:$D$1000,'KNUPIC daypart'!$A24,'HC RAW DATA'!$C$1:$C$1000,'KNUPIC daypart'!$B$1)/$N$4

Ah thank you!!

I am trying to change the anchored cell references using input from another cell. Would I be on the right track using INDIRECT as per the formula below if I applied that to all the ranges? with N2 and N3 being the row numbers for the start and end of the range?

=SUMIFS(INDIRECT("'HC RAW DATA'!$C"&VLOOKUP(N2,U:V,2,FALSE)):INDIRECT("'HC RAW DATA'!$C"&VLOOKUP(N3,U:W,3,FALSE)),$B$1,(INDIRECT("'HC RAW DATA'!$AE"&VLOOKUP(N2,U:V,2,FALSE)):INDIRECT("'HC RAW DATA'!$AE"&VLOOKUP(N3,U:W,3,FALSE))))

 

[etaf]

yes , that should work, although I suspect you can reduce the indirect()
i just made up a simple example to try

=SUMIFS(INDIRECT("$F$"&A2):INDIRECT("$F$"&A3),INDIRECT("$E$"&A2):INDIRECT("$E$"&A3),A1)

Such as
=SUMIFS(INDIRECT("$F$"&A3&":$F$"&A4),INDIRECT("$E$"&A3&":$E$"&A4),A2)

Book1
	A	B	C	D	E	F
1	Criteria	Result			Table
2	a	2			a	1
3	2	simplified			b	1
4	6	2			c	1
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUMIFS(INDIRECT("$F$"&A3):INDIRECT("$F$"&A4),INDIRECT("$E$"&A3):INDIRECT("$E$"&A4),A2)
B4	B4	=SUMIFS(INDIRECT("$F$"&A3&":$F$"&A4),INDIRECT("$E$"&A3&":$E$"&A4),A2)

 

[Bengo]

yes , that should work, although I suspect you can reduce the indirect()
i just made up a simple example to try

=SUMIFS(INDIRECT("$F$"&A2):INDIRECT("$F$"&A3),INDIRECT("$E$"&A2):INDIRECT("$E$"&A3),A1)

Book1
	A	B	C	D	E	F
1	Criteria	Result			Table
2	a	2			a	1
3	2				b	1
4	6				c	1
5					a	1
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUMIFS(INDIRECT("$F$"&A3):INDIRECT("$F$"&A4),INDIRECT("$E$"&A3):INDIRECT("$E$"&A4),A2)

Thank you so much, this has really helped!