Causing a formula to sumif if a row of dates is less than Today's date

[bearcub]

I asked something similar to this a couple of months ago but I have a better idea of what I need.

I have sheet that documents our companies new employees that receive a monthly draw (normally 5,000 USD for 3 months).

My heard row includes a date column with the end of each month for a year.

In the rows that follow I list the new employees who are receiving a draw. They all start at different times of the year but will only have at the most 3 months of draws.

What I want to do is add all the draws that have dates (in the column headers) that are less than the Today() function date.

I thought about using the sumif function but I realized this would have to be an array function. I also tried using the sumproduct function but it didn't work either - I get 0


SUMPRODUCT((B2:G2)<TODAY())*(B3:G3)

B2:G2 :1/31/2019,2/28/2019,3/31/2019, 4/30/2019,5/31/2019,6/30/2019
B3:G3      0       0         0          5000      5000      5000

Since today is June 1, the total should be 10,000. When Today's date is 7/1/2019 I would expect the total to be 15,000, etc.


Is the a formula that could do this in Excel dynamically so i don't have to manually update the total every month. I want to show how much draw is being paid up to todays date. I would like to have it update automatically.

Thank you for you help in advance,

Michael

 

[Roncondor]

It might be helpful, if you showed an example with more employees

Not sure why SUMIF or SUMIFS would not work

 

[bearcub]

Here is an example with 3 people. I was able use the sumproduct to get the dates to show true or false but I couldn't get the sum portion to work:

Name	1/31/19	2/28/19	3/31/19	4/30/19	5/31/19	6/30/19	7/31/19	8/31/19	9/30/19	10/31/19 11/30/19 12/31/19   Owed
Jamie				$5,000	$5,000	$5,000							    $10,000
John 					$5,000	$5,000	$5,000						    $ 5,000
Joe						$5,000	$5,000	$5,000					    $     0

I would like to exclude the amounts that are before today's date.

I've tried using an if statement, sumif and sumproduct.

I can get the criteria to calculate correctly using these approaches but I can't seem have it add correctly (or at all).

There is something that I',m missing in my formula logic ...

Thank you for your help.

Michael

 

[jtakw]

Hi,

According to your description, just SUMIF would work:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	1/31/2019	2/28/2019	3/31/2019	4/30/2019	5/31/2019	6/30/2019	7/31/2019	8/31/2019	9/30/2019	10/31/2019	11/30/2019	12/31/2019	Owed
2	Jamie				5000	5000	5000							10000
3	John					5000	5000	5000						5000
4	Joe						5000	5000	5000					0
Sheet666

Cell Formulas
Range	Formula
N2	=SUMIF(B$1:M$1,"<"&TODAY(),B2:M2)

Formula copied down.

 

[bearcub]

Ah, thak you. I was having a senior moment. I had the right criteria elements but I combined the first and second arguments into the first argument (i.e.Sumif(B2:G2<Today(), Today(), B3:G3).

Thank you for the correction

 

[jtakw]

You're welcome.