Forum Discussion

Copper Contributor

Sep 27, 2021

Solved

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...

Sumif.xlsx10 KB

excel

Formulas and Functions

Riny_van_Eekelen
Sep 27, 2021
GT1704 Perhaps the attached file will work for you. I've used SUMPRODUCT in stead of SUMIF.

Sumif.xlsx11 KB

GT1704

Copper Contributor

Sep 27, 2021

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

PeterBartholomew1

Silver Contributor

Sep 27, 2021

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.

Sumif.xlsx12 KB

SergeiBaklan
Diamond Contributor
Sep 27, 2021
PeterBartholomew1

As cosmetic

MONTH(Date)=9 => MONTH(Date)= MONTH(startMonth)
- PeterBartholomew1
  Silver Contributor
  Sep 27, 2021
  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.