Some Google Sheets / Excel Help please.

[keepngoal]

Need some google sheet help... even Excel, if that is the way it has to work.

Looking for a report, based on dates (variable entry on another tab), that will total (sum) up a defined list of options found in the column 'Description'

So for a date range of 9/1/18 to 9/13/18: "REV - Sale of Product" will show $0.00, "EXP - Professional Fees" will show $0.00, and "COGS - Inventory" will show $0.00. And "Initial Deposit" will show $50000.00

But a date of 9/1/18 t 9/14/18: "REV - Sale of Product" will show $699.99, "EXP - Professional Fees" will show $1000.25, and "COGS - Inventory" will show $13560.00. And "Initial Deposit" will show $50000.00

TIA

 

[Clone Head]

I am not sure if I understand your request, but in Excel the sumif function may provide a solution.

Another approach may to use a Pivot Table.

You can PM me if you want to discuss further.

 

[aeroclone]

Yeah, I second Clone Head here. I think SUMIFS is where you are headed. Set criteria for >= start date, <= end date, and description = whatever category you are after.

 

[Sigmapolis]

Always use SUMIFS.

Better to get used to it’s syntax than to switch back and forth from SUMIF.

 

[Cyclonesrule91]

SUMIFS rocks.... Use it all the time for stuff just like you are trying to get.

 

[keepngoal]

SUMIF... yes, however I am having a difficult time getting it 'reach' across a couple columns.

sumif(E2:E101,criteria from pulldown list,B2:C101)

Next step is to embed the the sumif for date criteria.

TIA.

 

[MBCyclones]

Here you go...
https://exceljet.net/excel-functions/excel-sumifs-function
First criteria is your date. Second criteria is your description.

 

[keepngoal]

Thank you all....

It seems the issue was the sum over two columns. B$2:C$101 didn't work ... but B$2:B$101 did ... So I thought my other logic was off, and therefore driving me crazy

Any thoughts on how to get more than one column in that formula?

=SUMIFS(Credit,Description,A2,Date,">="&StartDate,Date,"<="&EndDate)

where Credit is only one column.

 

[MBCyclones]

Thank you all....

It seems the issue was the sum over two columns. B$2:C$101 didn't work ... but B$2:B$101 did ... So I thought my other logic was off, and therefore driving me crazy

Any sight how to get more than one column in that formula?

=SUMIFS(Credit,Description,A2,Date,">="&StartDate,Date,"<="&EndDate)

where Credit is only one column.

=SUMIFS(Credit,Description,A2,Date,">="&StartDate,Date,"<="&EndDate) + SUMIFS(Debit,Description,A2,Date,">="&StartDate,Date,"<="&EndDate)

Use operators.

 

[keepngoal]

=SUMIFS(Credit,Description,A2,Date,">="&StartDate,Date,"<="&EndDate) + SUMIFS(Debit,Description,A2,Date,">="&StartDate,Date,"<="&EndDate)

Use operators.

I get that. However, it would pull those calcs in all places after a bulk c/p.

I think I'll use two separate calcs for the different columns.

 

[Sigmapolis]

SUMIFS rocks.... Use it all the time for stuff just like you are trying to get.

SUMIFS is indeed a great function.

You have to be careful with it, though. Its computation time is relatively high compared to something like, say, INDEX/MATCH or INDEX/MATCH/MATCH, so if you can do some concatenating to switch to an INDEX function instead of a SUMIFS, it is generally good form to do it. It takes up less space and it will generally process faster on you.

I dock my "freshmen" for unnecessary IFS functions.

 

[MBCyclones]

I get that. However, it would pull those calcs in all places after a bulk c/p.

I think I'll use two separate calcs for the different columns.

View attachment 58033

Yeah I’m confused