Question description:
This user has given permission to use the problem statement for this
blog.
I have a spreadsheet that totals invoices. A bunch of cells at the bottom of the spreadsheet calculates totals based on which department the invoice is charged to. I'd like to add a function where it also considers the date (so if it's an invoice in January, it goes to a January total, February, etc.). But I can't figure out how to write it properly...
Solved by K. J. in 27 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
09/02/2018 - 02:37
Welcome to excel got it pro.
Excelchat Expert
09/02/2018 - 02:37
I will assist you today.
User
09/02/2018 - 02:37
thanks
Excelchat Expert
09/02/2018 - 02:38
Would you please share the file with me? Cause I need to see how data are organized to write the formula.
User
09/02/2018 - 02:38
I could cut and paste the equation and some fields. Just can't share the whole file.
Excelchat Expert
09/02/2018 - 02:38
That would also help a lot.
User
09/02/2018 - 02:38
stand by
Excelchat Expert
09/02/2018 - 02:39
Sure, take your time.
User
09/02/2018 - 02:42
So I'd like to add a function to the equation in B89 that takes the date into consideration. It will only add it if it's January 1 - 31st, to this total.
User
09/02/2018 - 02:43
(date column in "s")
Excelchat Expert
09/02/2018 - 02:43
So, basically you want to add date condition to this existing formula right?
User
09/02/2018 - 02:44
that's correct.
Excelchat Expert
09/02/2018 - 02:44
Okay, gimme a minute please.
User
09/02/2018 - 02:44
sure and thanks.
Excelchat Expert
09/02/2018 - 02:50
This is the SUMIFS formula for sum by month =SUMIFS($L$2:$L$1212,$S$2:$S$1212,">="&B88,$S$2:$S$1212,"<="&EOMONTH(B88,0))
Excelchat Expert
09/02/2018 - 02:50
I have added it in B90.
User
09/02/2018 - 02:51
Is there a way to incorporate that to the end of the equation in B89? Or must it be separate?
Excelchat Expert
09/02/2018 - 02:52
Of course It can be. I just added it another cell seeing there are two different sumifs in B89.
User
09/02/2018 - 02:52
sorry. I'm no good at this.
Excelchat Expert
09/02/2018 - 02:52
Okay.
Excelchat Expert
09/02/2018 - 02:54
Where do you want to me incorporate the new condition? there are two different sumifs formula there.
User
09/02/2018 - 02:55
Can it go at the end of the equation?
Excelchat Expert
09/02/2018 - 02:55
another standalone sumifs joined by addition? Sorry, I don't actually get the full idea from your statement.
User
09/02/2018 - 02:56
Well, it adds the invoices to the equation only if it has been coded to certain accounts. What i was hoping was that it would keep that, but then only add it if it was in January (I'll end up doing this for each month).
Excelchat Expert
09/02/2018 - 02:57
I think I get it now. But let me know if I'm wrong.
Excelchat Expert
09/02/2018 - 02:59
Done
User
09/02/2018 - 02:59
thank you! HOw do i replace the month in the equation? Is that "AEOMONTH" or B88,0?
User
09/02/2018 - 03:01
thank you. i'm sorry i have to run into a meeting.
Excelchat Expert
09/02/2018 - 03:02
Like that, Add a new row of months.
Excelchat Expert
09/02/2018 - 03:02
Then use the cell reference.
Excelchat Expert
09/02/2018 - 03:02
is it clear?
Excelchat Expert
09/02/2018 - 03:03
Please close the session before you leave by clicking on the END SESSION button.
User
09/02/2018 - 03:03
thank you
Excelchat Expert
09/02/2018 - 03:04
A friendly reminder, if you don't close the session I'm stuck here and won't be able to help others.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
