adding/omitting/all the values prior to todays date

T

tleehh

using given formula by Otavio sum((B10:B20="s1")*(C10:C20<>"x")*A10:A20),
how can it add all the values in colume A that is prior to todays date (dates
are in colume D).
 
J

Joe User

tleehh said:
using given formula by Otavio
sum((B10:B20="s1")*(C10:C20<>"x")*A10:A20),
how can it add all the values in colume A that is
prior to todays date (dates are in colume D).
Click to expand...

=SUMPRODUCT((B10:B20="s1")*(C10:C20<>"x")*(D10:D20<TODAY()), A10:A20)

Or do you mean simply:

=SUMPRODUCT(--(D10:D20<TODAY()), A10:A20)

or alternatively:

=SUMIF(D10:D20, "<" & TODAY(), A10:A20)
 
B

Bernard Liengme

I hope Otavia gave you SUMPRODUCT not SUM for the formula you show
This should work
=SUMPRODUCT((D10:D20<TODAY())*(A10:A20)
or this
=SUMIF(D10:D20,"<"&TODAY(),A10:A20)
best wishes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

addding/omitting "x" 2
adding next years value 3
Return an absolutely empty cell ... but not "" 3
text to date..! 4
Adding multiple cells meeting criteria 4
Variable Sumproduct Range 6
How do count a cell range that include all dates prior to today? 3
Summing values for a range of dates 2

Top