SUM-IF Formula Issue: Results Not Displaying When Draggin Formula Across & Down

[jewls567]

I am using the following SUMIF formula in my spreadsheet:

=SUMIF('2020 CBAT Schedule'!$J:$J,!$A2,'2020 CBAT Schedule'!AX:AX)

When I attempt to drag the formula down it displays zeros instead of hours from the corresponding tabfrom which it should be sourcing the information from. The same happens when I try to pull the formula across.

When I make the criteria cell (i.e. Cell A2 within "Roster-Availability" tab) non-dymanic by removing the $ & !, then the data populates dragging downwards. However, when I try to drag it to the right to populate further date columns, it moves the criteria cell over since it's not static.

I'm not able to manually update this as I have lots of rows & columns & am looking for a way to fix my formula or possibly see if there is a different formula that could do what I am looking to achieve.

Please see the two screenshots below that show both tabs & point out the data range, criteria, & sum range that is used within the formula.

I appreciate as much help as anyone can provide. Thank you in advance!

Screenshots

 

[jasonb75]

The formula that you've posted is not the same as the one in your screen capture.

=SUMIF('2020 CBAT Schedule'!$J:$J,!$A2,'2020 CBAT Schedule'!AX:AX)

The bold ! should not be in the formula and the $ is missing from the formula in your screen capture, which is most likely the cause of the incorrect results.

 

[Fluff]

You shouldn't have the ! in front of $A2.

 

[jewls567]

Thank you so much, that fixed it for me! I took out the ! but kept the $ in front of A2 & it's now working! I sincerely appreciate your help!!

 

[jasonb75]

Looking at the way you describe things, I think you might be getting different reference types mixed up.

Dynamic would be a range that changes with criteria rather than when you drag a formula.
Static would be a range that never changes (even if rows / columns are inserted).
Relative ranges change as you drag a formula.
Absolute ranges remain unchanged as you drag.
Mixed ranges can use combinations of the above as needed.

The link below explains the more basic versions, the others will be easier to understand when you have a need for them.

Switch between relative, absolute, and mixed references - Microsoft Support

Use absolute or relative cell references in formulas, or a mix of both.

support.office.com

 

[Fluff]

Glad we could help & thanks for the feedback.