=sumifs ?

[zone709]

If I use this on my sheet right it works fine, but then I have to use formula on the line below with different name and this happens.


=SUMIFS(Data!E4:E569,Data!C4:C569,"Operator",Data!H4:H569,"Tower D") first line

=SUMIFS(Data!E5:E570,Data!C5:C570,"Super",Data!H5:H570,"Tower D") second line

You see the second line jumps E5:E570 C5:C570 H5:H570. If I change it back to E4:E569 etc on the rest I get #Value in box. What am I doing wrong. I cant jump up a number I need to stay in range thanks.

 

[Colorations]

=SUMIFS(Data!$E$4:$E$569,Data!$C$4:$C$569,"Operator",Data!$H$4:$H$569,"Tower D")

for the first line. Does that do anything for you?

 

[zone709]

Yeah that works

 

[Irrelevant]

You need to create absolute refrences for all the ranges here. you can do that by manually typing $ sign before the column and row indicator, or you could cycle to that by pressing f4 as you edit the formulat @ each range.
https://support.office.com/en-us/ar...ferences-dfec08cd-ae65-4f56-839e-5f0d8d0baca9

Once absolute, copying the formula won't change anything.

 

[zone709]

yeah now it doesn't change thanks. Why? cause of the $'s and if I want to add ot it I can just like this =SUMIFS(Data!$E$4:$E$569,Data!$C$4:$C$569,"Operator",Data!$H$4:$H$569,"Tower D")+SUMIFS(Data!$I4:$I$569,Data!$C$4:$C$569,"Operator",Data!$L$4:$L$569,"Tower D")

 

[zone709]

Hi I need to add another column to this to pull data from =SUMIFS(Data!$E$4:$E$569,Data!$F$4:$F$569,Data!$C$4:$C$569,"Operator",Data!$H$4:$H$569,"Tower D")

Adding it like that doesn't work need to pull also this column Data!$F$4:$F$569.

Thanks for any help here.