Sumif part of a number is found in a string

[freedannyv]

*Please note, that this is a question regarding Google Sheets, not Excel. Though in my experience, most of what I need works much the same across both.

I am trying to sum the amount of money spent on a particular date.

eg. Column B (the date column) shows the date when some money was spent, and may look like this at the end of the month;

B
5/5/2019
5/8/2019
5/8/2019
5/11/2019
5/12/2019

In order to create my daily report, I would like to sum the expense column (K5:K) based on the data in the date column. For instance, if the cells in the date column contain "/8/", then sum the equivalent cells together from (K5:K) and record that answer in my daily report cell. I would take a guess at the formula; sumif(Expense!B5:5, "*/8/*", Expense!K5:K), though this doesn't work.

Thank you for your help!

[FDibbins]

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Wed 22 May 2019) is actually 43607

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So, having said that, if your dates are real dates, and not text looking like dates, there is no /8/ in any of those cells.

That does not mean what you want to do, cant be done. On the contrary, it is relatively simple using SUMIFS() (not sure if Sheets has that function though)

=sumifs(sum-range, criteria-range1, criteria1, criteria-range2, criteria2....etc)

So, sumif(Expense!K:K, Expense!B:B, TODAY())

In retrospect, it seems lile you just need to use SUMIF based on TODAY()

[freedannyv]

Thank you for your help FDibbins. I have been working on it today and have found a workaround using =datevalue() in hidden cells. Next to the date cell on the daily report (which I will need to manually type each day (Ctrl and the expense sheet, which is updated immediately as we spend. Once these two share the same date value, I have a solid formula that totals the expenses from the appropriate cells. It looks like this;

=sumif(Expense!B5:B,A1,Expense!J5:J100)

A1 is the hidden datevalue() cell from the report.

Glad to get it fixed, and many many thanks for your help

Edit* I couldn't use TODAY() as we reopen the spreadsheet tabs regularly to cross check data, so it wasn't suitable for this purpose.

[FDibbins]

Happy to help