Forum Discussion

GT1704's avatar
GT1704
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...
excel
Formulas and Functions
PeterBartholomew1's avatar
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.

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 27, 2021

PeterBartholomew1 

As cosmetic

MONTH(Date)=9

=>

MONTH(Date)= MONTH(startMonth)
  • PeterBartholomew1's avatar
    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.

Resources