Formatting Issue

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
In an Excel "YTD report" which is sent to me, I am copying an entire sheet, and pasting into a blank sheet in my own seperate workbook. There are two columns of importance in this report": In column B are dates & times but only appearing as a dates (because row B is formatted as custom > dd/mm/yy). The 2nd column of importance is column AE which contain dollar amounts (of sales from the corresponding dates).

I'm trying to use SUMIF in my own sheet pull all totals from yesterday. The formula below is what I have:
=SUMIF('YTD Report'!B:B,TODAY()-1,'YTD Report'!AE:AE)

The formula however is returning "$0.00". I am certain that the reason for this has to do with row B on the pasted in sheet. Again, each cell in row B contains not just a date, but a time:
(01/02/21 8:25:43 AM) for example... even though only the date is visible when looking at the cell because its formatted to custom > dd/mmyy.
In fact, if I replace a couple of those cells in row b with a manually typed in "01/02/02", it then starts adding them up correctly. So my question is this: Is it possible to adjust my SUMIF formula to look only to the date in that cell without the need to make adjustments to the YTD report?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,402
Office Version
  1. 365
Platform
  1. Windows
If i understand you need a sumifs:

=SUMIFS('YTD Report'!B:B,'YTD Report'!AE:AE,">="&TODAY()-1,'YTD Report'!AE:AE,"<"&TODAY())
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
Thanks Steve. Unfortunately that returns "$0.00" as well. What exactly it that formula doing/ If I know that, then maybe I can figure out if it's something with the report itsef, or perhaps I miscommunicated some info. Thx
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
Another option to consider:
Excel Formula:
=SUMPRODUCT(--(INT('YTD Report'!B:B)=TODAY()-1),'YTD Report'!AE:AE)
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I think Steve's formula just had a couple terms switched:
Excel Formula:
=SUMIFS('YTD Report'!AE:AE,'YTD Report'!B:B,">="&TODAY()-1,'YTD Report'!B:B,"<"&TODAY())
 
Solution

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
Thx KRice, but I don't think that's addressing the root of the formatting issue which seems to be not reading the date part only in column B, because this returns a #VALUE! error.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,402
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are you sure your dates are dates? In a spare cell by any date test that cell using =ISNUMBER(A1) but change the A1 to the real cell address
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
I think Steve's formula just had a couple terms switched:
Excel Formula:
=SUMIFS('YTD Report'!AE:AE,'YTD Report'!B:B,">="&TODAY()-1,'YTD Report'!B:B,"<"&TODAY())
That got it! Bam!Thank you KRICE! (and Steve The Fish!)
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,402
Office Version
  1. 365
Platform
  1. Windows
To take something away you use a conditional sum to say 'if this cell equals this then sum the adjacent cells'. A date and time is not equal to just a date so what you originally wrote could never work. What these new formulas do is say is the date/time greater than today-1 but also less than today. This will encompass all date/times within any particular date.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top