SUMIFS with Multiple Criteria

Hello!

My ultimate goal is to be able to get a sum of the inventory per item, by month. For example, I would like a formula to return the amount of Control Panels there are in June 2020. Based on the screenshot below, the result I should receive is "2" as is only one project in June 2020 and the project has 2 Control Panels listed.

This is the formula that I am using but unfortunately is returning 0:

=SUMIFS([Project Name]:[Project Name], FDOB:FDOB, IFERROR(MONTH(@cell), 0) = 5, FDOB:FDOB, "Control Panel")

If anyone can provide assistance on this, that will be greatly appreciated!

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    The problem lies in that your date doesn't exist on the same row as your control panel number.You would need your date to be replicated in a helper column perhaps that is adjacent to the control panel row. You could do a manual link of the data in the date cell to a helper column, but sumifs are always looking at data in the same row. Based on your formula you are looking at a sum of every row that has control panel in the FDOB column and the month of 5 in the FDOB column. You need to replicate the date into a helper column to the right of the inventory list. Then do a Sumif with the date column and the FDOB column and that should do the trick.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!