Excel question (1 Viewer)

[Captain Quinoa]

I'm sure that I'm missing something obvious here, but I can't make this work.

Say on Sheet1 I have downloaded all of my open orders so each row represents an open order line. On Sheet2 I want to display this by item. So I have my part numbers listed in a column and I use a SUMIFS to total up the orders by item. Now I want to show how many are past due. So I add 'Sheet1'!$F:$F,"<6/22/2015" to the SUMIFS so that it only pulls orders due prior to today. This works fine, but is there a way to automate it using TODAY() or something else so that I don't have to go in every day and change the date in the formula?

TIA

 

[Ned]

Use a pivot table instead of creating a bunch of SUMIFs.

 

[Captain Quinoa]

Use a pivot table instead of creating a bunch of SUMIFs.

There's a lot of other info/columns on sheet 2

 

[wilked]

Well, I don't have the 15 mins to try and interpret what the hell you want, but I can say I read enough to know you are doing it wrong

Put it into a google sheet and share the sheet, copy link here, will make a lot more sense

 

[bcdjr1]

Today() will return today's date.

 

[roadkill1292]

Today() will return today's date.

How are "Today" and "Now" different?

:learning:

 

[Long Ball Larry]

did you try putting in Today() in place of 6/22/15 in that formula? I would assume that would work.

If not, you should be able to have a cell with =Today() in it and use that as the reference in the formula.

 

[bcdjr1]

Now() returns date and time. Today() just the date.

 

[Spin]

I'm sure that I'm missing something obvious here, but I can't make this work.

Say on Sheet1 I have downloaded all of my open orders so each row represents an open order line. On Sheet2 I want to display this by item. So I have my part numbers listed in a column and I use a SUMIFS to total up the orders by item. Now I want to show how many are past due. So I add 'Sheet1'!$F:$F,"<6/22/2015" to the SUMIFS so that it only pulls orders due prior to today. This works fine, but is there a way to automate it using TODAY() or something else so that I don't have to go in every day and change the date in the formula?

TIA

Using your example, =sumif('Sheet1'!$F:$F,"<" & TODAY(), [sum range])

 

[Captain Quinoa]

I'm sure that I'm missing something obvious here, but I can't make this work.

Say on Sheet1 I have downloaded all of my open orders so each row represents an open order line. On Sheet2 I want to display this by item. So I have my part numbers listed in a column and I use a SUMIFS to total up the orders by item. Now I want to show how many are past due. So I add 'Sheet1'!$F:$F,"<6/22/2015" to the SUMIFS so that it only pulls orders due prior to today. This works fine, but is there a way to automate it using TODAY() or something else so that I don't have to go in every day and change the date in the formula?

TIA

Using your example, =sumif('Sheet1'!$F:$F,"<" & TODAY(), [sum range])

That was it. Basically I was forgetting the ampersand

I won't be having any more children, but I will name my next dog after you.

Muchas gracias

 

[roadkill1292]

Now() returns date and time. Today() just the date.

Grunt. Thanks.