SOLVED

New Contributor

# sumif and date range

Hi,

I was wondering if someone can help me with a Sumif and date range formula?

I wanted to add invoices for two date ranges for one client.

I've attached a file with some data in it that explains what I want to do.

Gina

7 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

# Re: sumif and date range

@GT1704 Perhaps the attached file will work for you. I've used SUMPRODUCT in stead of SUMIF.

# Re: sumif and date range

Thank you Riny_van_Eekelen!
This works very well. I have not used SUMPRODUCT before and this is an excellent alternative.
Cheers

# Re: sumif and date range

C12: =SUMIF(D2:D4,"<="&"15.09.2021",H2:H4)

C13: =SUMIF(D2:D4,">="&"16.09.2021",H2:H4)

you need to press Ctrl+Shift+Enter to enter them

Create an array formula

Array formulas are powerful formulas that enable you to perform complex calculations that often can’t be done with standard worksheet functions. They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you need to press Ctrl+Shift+Enter to enter them.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

# Re: sumif and date range

Thank you NikolinoDE for taking the time to answer my question.
This works very well.

# Re: sumif and date range

Others have already answered but since I laid out the sheet before going to lunch ...

I would most likely go with SUMIFS, but there are alternatives

``````= SUMPRODUCT(
IF(
(Description=ClientName)*
(MONTH(Date)=9)*
(DAY(Date)<=15),
Amount))``````

The SUMPRODUCT is simply a wrapper that makes legacy Excel process arrays correctly; I reality, I use SUM.  By the way, I needed to re-commit your dates because they came over as text.

# Re: sumif and date range

As cosmetic

``````MONTH(Date)=9

=>

MONTH(Date)= MONTH(startMonth)``````

# Re: sumif and date range

Agreed. I guess I was mesmerised as to how the OP got March figures out of September dates (text at that). For me semantics and legibility are a critical element of code development but I have to recognise that others prioritise brevity or the speed of code creation.