Formula Support

[jcgraham0007]

HI,

I'm new to this so apologies if the following does not make sense.

I'm creating a spreadsheet that is pulling data based on the product name and date range using a sumif statement. (The data is on the same workbook), under a separate sheet)

The statement is =SUMIFS(Data!$I:$I, Data!$A:$A, "="&'Report'!D9, Data!$H:$H, ">="&'Report'!$F$7, Data!$H:$H, "<="&'Report'!$F$8)

Data I - Pulling quantity - Data A - Searching the product code - Data H - The date range from and checking the date cell - Data H - The date range to and checking the date cell

This works for every date in June and from the 13th of July, However, anything from the 1st of July to the 13th of July brings back zero, I have checked the date format on both the data page and the spreadsheet and copied the formula to make sure there is no issue (Changing the formula to match the correct cells) - I have changed the dates to text format as I need to ensure they are US dates to match against the data report.

I'm now pulling my hair out as I can not see why it not pulling the data through for those dates and wondered if a multiple pair of eyes can see something I cant see

I've uploaded an image of the spreadsheet to give you an idea from the dates and the data, the formulas in all of the cells are identical (I have changed the cell reference for the name and dates)

 

[jasonb75]

I've finally sussed what was wrong. The sumif statement was asking it to look for data between 2 dates. The data was coming back as zero because one of the dates listed was not on the data report, therefore it was unable to complete loop.

A single day missing from the report shouldn't cause the formulas to fail. With the exception of June 23, all of the date ranges in the summary cover multiple days so you should only see 0 if there is no data for any of the days in a specific period.

For today+8, if there is no data for 06-30-2020 then the formula should still show the result for 07-01-20, likewise if there is no data for 07-01-2020 then it should still show the result for 06-30-2020. If it is not doing that then you still have problems with dates that are not valid.

getting the formula to see the date in the cell and pull a result not the formula today().

Not sure that I follow what you mean there, a formula that looks at another formula always sees the result, not the actual formula. If you enter =TODAY() into A1 then any formula that looks at A1 will see today's date, not the formula that is in A1.

 

[jcgraham0007]

Then maybe I'm doing it wrong, I add today()+ and data disappears (when I click on the cell the formula bar shows today() not the date, it maybe today() is defaulting to a uk date. If I put the actual date as text or a us date the data pulls through.

I changed the 06-30-2020 to 07-01-2020 and data appeared. When I looked at the report the 06-30-2020 was not listed hence my message.

 

[jasonb75]

If I put the actual date as text or a us date the data pulls through.

If the date is text then it is not a date.

From your post it appears that UK dates are your default format, if that is the case then anything entered in US format is not valid. 07-01-2020 is evaluated as Jan 7th, not July 1st. Because 06-30-2020 is not a valid UK date it is evaluated as text.

A valid UK date formatted in US format will work, a US date will not because it is text. If you want to use proper US dates then you will need to change the regional settings of your computer to US format via the windows control panel. This will mean using US dates for everything, not just this one sheet.

 

[jcgraham0007]

As I hot swap work stations this is not feasible. At least if I change the dates missing from the report its pulling the right sum (I've checked against the actual data) its probably the best I'm going to get without changing the settings.

Thankyou so much for your help though, I would have got to this point without your help.

 

[jcgraham0007]

As I hot swap work stations this is not feasible. At least if I change the dates missing from the report its pulling the right sum (I've checked against the actual data) its probably the best I'm going to get without changing the settings.

Thankyou so much for your help though, I would not have got to this point without your help.