# Sumifs Function Query

#### Zambuki

##### New Member
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!

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Special-K99

##### Well-known Member
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

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

Last edited:

##### MrExcel MVP
In which cell are you implementing the formula?

#### Zambuki

##### New Member
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.

#### Special-K99

##### Well-known Member
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

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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,381
Messages
5,853,490
Members
431,593
Latest member
AAAaahhh

### 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.

### Which adblocker are you using?

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

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