Sumifs Function Query

Zambuki

New Member
Joined
Jul 5, 2016
Messages
4
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
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
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
 

Zambuki

New Member
Joined
Jul 5, 2016
Messages
4
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top