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

jewls567

New Member
Joined
Jun 1, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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
Screenshot 1.png

Screenshot 2.png
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
You shouldn't have the ! in front of $A2.
 
Upvote 0
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!! :)
 
Upvote 0
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.

 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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
Back
Top