Thread: Sumifs lower than a given day Thanks:  1 Post #5343285 (1) Likes:  1 Post #5343285 (1)

1. Sumifs lower than a given day

Hello
I'm trying to do a sumifs formula based on 2 criterias. The first is a name.

the second one I can't do is when the date DAY is lower than 15.

For example in the table below I want to get the sum of A only lower than 15, the result should be 2.000

I was trying something (hardcoded just for clarity) like: +sumifs(value, name, "A", day(date),"<15")
But it doesn't go through, also thought to put them between {} but it doesn't work.
I don't want to do a 4th column with the days, but if its not possible I guess I can do that.

 Name Date Value A 09/11/2019 1000 A 09/13/2019 1000 A 09/16/2019 1000 B 09/14/2019 1000 B 09/16/2019 1000

Thanks for the help

2. Re: Sumifs lower than a given day

 C D E F 1 Name Date Value 2 A 9/11/2019 1000 2000 3 A 9/13/2019 1000 4 A 9/16/2019 1000 5 B 9/14/2019 1000 6 B 9/16/2019 1000

 Cell Formula F2 =SUMPRODUCT((C2:C6="A")*(DAY(D2:D6)<15),E2:E6)

Excel tables to the web >> Excel Jeanie HTML 4

3. Re: Sumifs lower than a given day

Perfect!
Thanks a lot!

4. Re: Sumifs lower than a given day

Try

 A B C D 1 Name Date Value 2 A 11/09/2019 1000 2000 3 A 13/09/2019 1000 4 A 16/09/2019 1000 5 B 14/09/2019 1000 6 B 16/09/2019 1000

 Cell Array Formula D2 {=SUM(IF(A2:A6="A",IF(DAY(B2:B6)<15,C2:C6)))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

5. Re: Sumifs lower than a given day

Originally Posted by el_ja
Perfect!
Thanks a lot!
You are welcome - thanks for the reply.