Sumproduct or array formulas - Help
Hi All,
I am using mac Excel 2011. Yes I know it's old.
A) If I wanted to sum the elements in column Q, if the dates in Column D and E were between a given range, what is the best way to do that please? Would we use Array formulas (CTRL, SHIFT, ENTER) or SUMPRODUCT, etc. E'g sum the rows in column Q if D=>first date in range and E<=last date in range.
B) In addition to this can I add up the rows in Column Q using 2 date ranges, e.g. if D to E is in range 1 OR if D to E is in range 2
e.g sum up Column Q if (D to E is in range 1st April to 15th April ) OR (D to E is in range 13th May to 30th May)
In this example we would have (Q168 + Q169 + Q170 + Q182 + Q187) + (Q174 + Q175 + Q176 + Q177 + Q178 + Q179 + Q183 + Q188)
Thanks
A) One way to sum the elements in column Q based on a date range in columns D and E is to use the SUMPRODUCT function. This function allows you to sum the values in a column based on multiple criteria. Here’s an example formula that sums the values in column Q if the dates in column D are greater than or equal to a start date and the dates in column E are less than or equal to an end date:
=SUMPRODUCT((D:D>=start_date)*(E:E<=end_date)*(Q:Q))
In this formula, start_date and end_date are the dates that define your date range. Make sure to replace them with the actual dates or cell references that contain the dates.
B) To sum the elements in column Q based on two different date ranges, you can use two SUMPRODUCT functions and add them together. Here’s an example formula that sums the values in column Q if the dates in columns D and E fall within either of two date ranges:
=SUMPRODUCT((D:D>=start_date1)*(E:E<=end_date1)*(Q:Q))+SUMPRODUCT((D:D>=start_date2)*(E:E<=end_date2)*(Q:Q))
In this formula, start_date1, end_date1, start_date2, and end_date2 are the dates that define your two date ranges. Make sure to replace them with the actual dates or cell references that contain the dates.
I hope this helps!
