Tuesday, October 10, 2017

yogi_SUMIFs to match year+month with year+month+date criteria

Google Spreadsheet   Post  #2265

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Oct-10-2017
Question by Jet Vincenzo
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/xwP_vQ9PcTE;context-place=forum/docs
Need help using SUMIFs to match year+month with year+month+date criteria
Hi Google Sheets Wizards!

I am trying to create a monthly summary of how much of a certain part number was sold, so, I'm using SUMIFs to match the part number and the year+month in the Data Table, which shows transactions by part number and by day/date. 


The equation I have for the Output table is: =SUMIFS($D$3:$D$12,$C$3:$C$12,C$15,$B$3:$B$12,$B16). However, this equation only sums the values if the dates are an exact match. The dates in the Output Table are all 2017-XX-01, but formatted to show only the year and month.

My original workaround, which was pretty ugly, was to create a "dummy" column to the right of the Data Table date column and used it to convert the dates to match the first of the month, and then used SUMIFS on that column.

How can I fix my SUMIFs equation to make this work and without having to create that dummy column? What is the cleanest or most elegant solution to what I'm trying to do? I was reading posts about Arrays Formulas, Filter, and Queries, but I've never used those before and they seem much more complicated and might slow my Sheets file down.

Would appreciate any help you guys can provide.

Thank you!!!

Here's the link to the file:

No comments:

Post a Comment