Multiple conditions

[Muthukrishnan V]

Excel 2007.

Column C5 to C84 contain dates in DD-MM-YYYY format
Column D5 to D84 contain amount in Numerical format (Rund nmbers, No digits)
Column E5 to E84 contain dates in DD-MM-YYYY format.

My requirement: I want sum total of amount, where C4:C84 is =>01-4-2018 <=31-03-2019 and
where E5:E84 is => 01-04-2023 <=31-03-2024.

Kindly provide formula to achieve output.
Thank you

 

[Scott T]

=sumproduct(d5:d84,--(c5:c84>=date(18,4,1)),--(c5:c84<=date(2019,3,31)),--(e5:e84>=date(23,4,1)),--(e5:e84<=date(2024,3,31)))

 

[Joe4]

Use a SUMIFS formula.

See:
https://www.extendoffice.com/documents/excel/1631-excel-sum-values-between-two-dates.html
and
https://exceljet.net/excel-functions/excel-sumifs-function

 

[Scott T]

I though SUMIFS was introduced after 2007 but looks like it is in 2007. I would SUMIFS rather then SUMPRODUCT.

 

[Muthukrishnan V]

Thank you for resolving my request.

Now alternatively, I want to improve the SAME database
with helper cells.
Additions:
In addition to my database in C5:E84 as above, I have added
input in Col G5:G84. G5:G84 contain text either "current" "old" and
some cells blank.
H 101 (helper cell) contains date less than H102 in dd-mm-yyyy (one of the dates in C5:C84);
H 102 (helper cell) contains date greater than H101 in dd-mm-yyyy (one of the dates in C5:C84).
H103 (helper cell) contains a text "Current" (one of the text from G5:G84)
Based on the values in helper cells H 101 to H 103, i want sumproduct of D5:D84.
Kindly help.

 

[Joe4]

You can add more conditions to the current SUMPRODUCT formula and SUMIFS formula. Just follow the same format, and keep adding the additional conditions you need.