Hi all,
Thank you in advance.
i am trying to add up with criteria from a row and column matching to a specific date range. I have tried to write my my own formula but it has not worked.
=SUMPRODUCT(('JETS REF & STARTS RAW Data'!$B$6:$ZZ$18)*('JETS REF & STARTS RAW Data'!$A$6:$A$18=A6)*('JETS REF & STARTS RAW Data'!$B$5:$ZZ$5>=DATEVALUE(C23))*('JETS REF & STARTS RAW Data'!$B$5:$ZZ$5<=DATEVALUE(D23)))
i have attached a sample of the data
Rather than the older SUMPRODUCT which was extremely valuable before the SUMIFS & COUNTIFS function, use this now instead.
=SUMIFS($B6:$AF6,$B$5:$AF$5,">="&C$23,$B$5:$AF$5,"<="&EOMONTH(C$23,0))
Last edited by Richard Buttrey; 11-11-2021 at 09:11 AM.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Try
=SUMPRODUCT(('JETS REF & STARTS RAW Data'!$B$6:$ZZ$18)*('JETS REF & STARTS RAW Data'!$A$6:$A$18=$A6)*('JETS REF & STARTS RAW Data'!$B$5:$ZZ$5>=C$23)*('JETS REF & STARTS RAW Data'!$B$5:$ZZ$5<D$23))
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
is there way make it work for range of a6 to a18 as i want to pull down the formula.
If you copy the formula in post#2 down it will sum the individual rows 6:18
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks