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

jewls567

New Member
Joined
Jun 1, 2020
Messages
11
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,939
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,939
Office Version
  1. 365
Platform
  1. Windows
You shouldn't have the ! in front of $A2.
 

jewls567

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

Well-known Member
Joined
Dec 30, 2008
Messages
11,939
Office Version
  1. 365
Platform
  1. Windows
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.

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,939
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,530
Messages
5,596,699
Members
414,088
Latest member
rodriboraun

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