SUMIFS - Based on Vendor & Date Criteria

[HILLP]

Hi, I am looking to see why the formula below is returning a #Value response in the cell.

I am basically trying to sum a cost column based on 2 criteria-- 1- Date & 2- name of company.

If you see something missing from the formula I would appreciate a fix.



=SUMIFS('Planned-Manpower Cost'!YF$9:YF$142,'Planned-Manpower Cost'!$B$9:$B$142,'Summary- Roll Up'!$B5,'Planned-Manpower Cost'!$YF$5:$AYO$5,'Summary- Roll Up'!C$2)

 

[Fluff]

Hi & welcome to MrExcel.
All the ranges in SUMIFS need to be the same size, try

=SUMPRODUCT(('Planned-Manpower Cost'!AY$9:YF$142)*('Planned-Manpower Cost'!$B$9:$B$142='Summary- Roll Up'!$B5)*('Planned-Manpower Cost'!$YF$5:$AYO$5,'Summary- Roll Up'!C$2))

Also if the formula is on the Summary- Roll Up sheet you should remove the sheet name from the formula for those cells.

 

[JEC]

Your criteria ranges are not equal lengths

 

[HILLP]

Your criteria ranges are not equal lengths

So one criteria is puling from a the same row different columns (YF5: AYO5) this is the date portion. While the second criteria is pulling from the same column different rows (B9:B142) this is the vendor name column. Can this not work, or will the SUMIF formula not be able to do this?

 

[Fluff]

All the ranges in a SUMIFS need to be the same size. Did you try my suggestion?

 

[HILLP]

All the ranges in a SUMIFS need to be the same size. Did you try my suggestion?

Yes I did, and thank you for suggestion. I could not get the formula to work in this scenario. I am thinking a SUMIF with an VLOOKUP might be what is needed

 

[Fluff]

There's a typo in my formula, it should be

=SUMPRODUCT(('Planned-Manpower Cost'!YF$9:AYO$142)*('Planned-Manpower Cost'!$B$9:$B$142='Summary- Roll Up'!$B5)*('Planned-Manpower Cost'!$YF$5:$AYO$5,'Summary- Roll Up'!C$2))

 

[HILLP]

To anyone following the thread the formula below was the fix:

=SUMIFS('Planned-Manpower Cost'!YF$9:YF$141,'Planned-Manpower Cost'!$B$9:$B$141,'Summary- Roll Up'!$B4)

 

[Fluff]

So the YF5:AYO5 part is totally irrelevant?