Teams
Forum List
Toggle sidebar

Some Google Sheets / Excel Help please.

keepngoal

keepngoal

Utter Nonsense!
Staff member
Bookie
Jun 20, 2006
37,968
22,118
113
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

Journal Entries - Google Sheets (2).png
 
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.
 
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.
 
Always use SUMIFS.

Better to get used to it’s syntax than to switch back and forth from SUMIF.
 
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.
 
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.
 
Last edited:
keepngoal said:
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.
Click to expand...

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

Use operators.
 
  • Friendly
  • Like
Reactions: keepngoal and octaviouspoon
MBCyclones said:
=SUMIFS(Credit,Description,A2,Date,">="&StartDate,Date,"<="&EndDate) + SUMIFS(Debit,Description,A2,Date,">="&StartDate,Date,"<="&EndDate)

Use operators.
Click to expand...
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.

Journal Entries - Google Sheets (3).png
 
Cyclonesrule91 said:
SUMIFS rocks.... Use it all the time for stuff just like you are trying to get.
Click to expand...

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. :)
 
  • Agree
Reactions: Cyclonesrule91
You must log in or register to reply here.

Help Support Us

Become a patron
Top Bottom
Back