Forum Discussion
Figuring the best way to use SUMIFS/DSUM with dates and criteria
Hi Everyone, I'm currently working on a spreadsheet for my job. The goal of the current equation is to look at the criteria of the columns and give a total based on a certain month and Species of wood. (For example I want to find the total number of Pop + RO or Stock at 1 for the month of April.)
I have figured out how to find the total for April which was the formula "=SUMIFS(F2:F10834,C2:C10834, ">=" &J4,C2:C10834, "<=" &EOMONTH(J4,0))" but when I add another criteria Ex, "=SUM(SUMIFS(F2:F10834,I$2:I$1048576, 1,C2:C10834,">="&J4,C2:C10834,"<="&EOMONTH(J4,0)))" the equation doesn't work. What is going wrong where this fails. I also tried to use DSUM but couldn't figure out how it works. What formula would be my best bet in figuring this out. The 1 and 0 (I column) which is what I'm trying to figure.
2 Replies
SUMPRODUCT can do this.
SUMPRODUCT function - Microsoft Support
=SUMPRODUCT(((K4:K36="A")+(K4:K36="B"))*(M4:M36<EOMONTH(TODAY(),0))*L4:L36)
I can't rebuild your sheet but this formula works in the sample file and might answer the question.
In your second formula the ranges are not the same e.g. F2:F10834 and I2:I1048576 are different. The first has 10833 rows the second has 1048575 rows which doesn't work.
- I guess my more specific question would be a good way to filter the dates and select a month as a whole while keeping the dates as is. I hope this will be used in years to come.
