sumif and date range

[GT1704]

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.

Thanks in advance for your help!

Gina

[#2 · Accepted solution]

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

[#3]

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

[#4]

@GT1704 

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.

[#5]

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

[#6]

@GT1704 

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.

[#7]

@Peter Bartholomew 

As cosmetic

MONTH(Date)=9

=>

MONTH(Date)= MONTH(startMonth)

[#8]

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.