Sumproduct (date bracketed)

  • Thread starter Thread starter GerryK
  • Start date Start date
G

GerryK

Good Day,
Could someone please help to correct the formula below?

While in Sheet1, I would like to add from another sheet
named Data between dates I choose in Sheet1 B18 (later
date) and in A18 (an earlier date)

My set up is:
Data! (column D) are the dates.
C18 is an activity code choice referenced Data!(column B).
Data!(column J) are the numbers I'd like to add up.

In B19 I have (array entered)
=SUMPRODUCT((MONTH('Data'!$D$3:$D$100)<B18>A18)*('Data'!
$B$3:$B$100=C18)*'Data'!$J$3:$J$100)

All I get right now is the total YTD for the activity code
chosen in C18.

Basically, I cannot bracket my addition based on date
selection. If I could get help doing just that it would be
appreciated and I can work out the rest.

TIA
 
Hi
try
=SUMPRODUCT((MONTH('Data'!$D$3:$D$100)<B18)*(MONTH('Data'!$D$3:$D$100)>
A18)*('Data'!$B$3:$B$100=C18),'Data'!$J$3:$J$100)
 
=SUMPRODUCT((MONTH('Data'!$D$3:$D$100)<B18)*(MONTH('Data'!$D$3:$D$100)>A18)*
('Data'!
$B$3:$B$100=C18)*'Data'!$J$3:$J$100)
Bob Umlas
Excel MVP
 
Thank you Frank! also Bob.
Very much appreciated!
-----Original Message-----
Hi
try
=SUMPRODUCT((MONTH('Data'!$D$3:$D$100)<B18)*(MONTH('Data'! $D$3:$D$100)>
A18)*('Data'!$B$3:$B$100=C18),'Data'!$J$3:$J$100)


--
Regards
Frank Kabel
Frankfurt, Germany


.
Click to expand...
 
Back
Top