SumIf not working

piannetta

New Member
Joined
Aug 27, 2002
Messages
36
Hi,

I have the following SumIf formula that's not working as I expect it to:

=SUMIF(QRYTasksRcvd!$A$2:$A$1779,'Daily Tracker'!D7,QRYTasksRcvd!$B$2:$AE$1779)

The first argument is referring to the first column of a table of data where column A is the date.

The second argument is the date I'm looking up.

The third argument is the column range of the data I want to sum for the row where the date matches.

It seems the formula is only returning the value in column B, not the sum of values of columns B through AE.

Could someone help diagnose what I'm doing wrong please?

Thanks,
Pete
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try...

=SUM(IF(QRYTasksRcvd!$A$2:$A$1779='Daily Tracker'!D7,QRYTasksRcvd!$B$2:$AE$1779))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,624
The problem is that SUMIF requires the ranges to be the same size, you can't have the sum range with extra columns. If you only have one date that will match, you can use this:

=SUM(INDEX(qrytasksrcvd!$B$2:$AE$1779,MATCH('Daily Tracker'!D7,qrytasksrcvd!$A$2:$A$1779,0),0))

If you have multiple matching dates, we'll need to move to an array formula, like Dominic's.
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,543
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
This might work for you, if you have the same date more than once:

=SUM(IFERROR(MMULT(TRANSPOSE(--(QRYTasksRcvd!$A$2:$A$1779='Daily Tracker'!D7)), QRYTasksRcvd!$B$2:$AE$1779), 0))

Use Ctrl+Shift+Enter to confirm.
 
Last edited:

piannetta

New Member
Joined
Aug 27, 2002
Messages
36
The problem is that SUMIF requires the ranges to be the same size, you can't have the sum range with extra columns. If you only have one date that will match, you can use this:

=SUM(INDEX(qrytasksrcvd!$B$2:$AE$1779,MATCH('Daily Tracker'!D7,qrytasksrcvd!$A$2:$A$1779,0),0))

If you have multiple matching dates, we'll need to move to an array formula, like Dominic's.

Thanks Eric, I only have one matching date so your formula does the trick, thank you so much.

I had checked the online help for this function and it seemed to indicate you could have multiple columns, so thanks for clearing that up.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,732
Messages
5,597,798
Members
414,176
Latest member
LK88

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