Sumifs Function Query

[Zambuki]

Hi Guys,

I have a sumifs function sorted but a very large sheet to populate with the formula. The formula I have is as follows:

=SUMIFS(Scaffolds!$AG$11:$AG$4136,Scaffolds!$E$11:$E$4136,"Basement 03",Scaffolds!$F$11:$F$4136, "Column Work")

Instead of now copying the formula down and having to manually type in "Basement 03","Basement 02" etc. Is there a way I can copy the formula with down with a single reference to the Heading? i.e "=A3" which is Basement 03 or "=A4" which is Basement 02 etc.

Much appreciated!

 

[Special-K99]

Try

=SUMIFS(Scaffolds!$AG$11:$AG$4136,Scaffolds!$E$11:$E$4136,INDEX(A3:A1000,ROWS($1:1),1),Scaffolds!$F$11:$F$4136, "Column Work")

where A3:A1000 equal "Basement 03", "Basement 04" etc
and copy down the column as far as you need

 

[Oscar Cronquist]

=SUMIFS(Scaffolds!$AG$11:$AG$4136,Scaffolds!$E$11:$E$4136,A3,Scaffolds!$F$11:$F$4136, "Column Work")

 

[Aladin Akyurek]

In which cell are you implementing the formula?

 

[Zambuki]

Thanks but not coming right with it. A2:A38 are the varying levels, then column C1:R1 are another set of headings. C2:R38 need to all be populated with the SUMIFS formula.

Example:

Row 3 = Basement 03, Column C1 is Column Work, D1 is Brickwork, E1 is Lift shaft etc. In column C; I need to be able to drag down the formula from C2:C38 but instead of having to edit each row down "Level 1", "Level2" etc I need a formula to recognise that A2=Level 1, A3= Level 2 etc in order to complete its search in another tab for the various criteria.

Hope that makes more sense.

Thanks for your efforts!

 

[Special-K99]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.