SumIf not working

[piannetta]

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

 

[Domenic]

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]

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]

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.

 

[piannetta]

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.

 

[Eric W]

Glad we could help.