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)

 

[jcgraham0007]

Here is a copy of the data it is looking at

 

[jasonb75]

The dates in your second screen capture are aligned to the left of the cells which suggests the possibility that they are not valid dates.

Try changing the cell format of the dates to General / Number. If they stay as dates then they are not valid, if they change to numbers ~44000 then they are valid.

 

[jcgraham0007]

Hi Jason,

Thank you for the info, I did change to General/Numbers and they remained the same, however, if you look at the first attachment the same data is being pulled through for some of the fields and it's only from the 1st to the 13th that didn't pull through.

is their a formula I can use that looks pulls through column I on the basis it matches column A (and the relevant cell) and H (and the date range(s))

So I am looking at the total quantity to be pulled if the product is being made between two dates.

Thanks in advance

 

[jasonb75]

A formula will not work for dates that are not in a valid format. The dates that you have are text strings, which means that they may not evaluated in the correct order when using a range of dates.
Although some of the dates appear to be pulling results those results will possibly be incorrect, or if they are correct then it will be by luck more than judgement. The problem will most likely be exaggerated if the dates do not match the regional settings of your computer. The data is formatted with US dates, so UK settings will only recognise dates where the day is less than 13.

The dates in your sheet can be 'fixed' with text to columns by selecting a single column of dates, clicking 'Text to Columns' (on the data tab), clicking 'Next' twice, changing the 'Date' dropdown to 'MDY' then clicking Finish.

Note that unless you correct the source of the data you will need to follow the above steps for any newly added data. Re-applying it to existing data a second time will create additional errors.

 

[jcgraham0007]

Hi Jason,

That makes sense so I have changed to text to columns and as you suggested the dates changed to numbers, I have then custom formatted the column to us dd-mm-yyyy and most of the fields have populated data (different to before which confirms your suggestion the original data may have been wrong) with the exception of 2 columns which although the date has data does not seem to be pulling through.

I have attached the report so you can see what happens when the complete text to columns (Some go to Uk Format and Some go to US - I have to change via custom to dd-mm-yyyy) still these 2 fields pull no data through even when I can see there is data to pull through.

I am not sure when I pull this data through its raw data as the file size is 8kb so I wonder if the formats been lost and there is no way of automating this format.

 

[jasonb75]

It is not easy to see exactly what the problem is from screen captures, it appears that some of the dates have not been corrected by text to columns.
Although you have them custom formatted as dd-mm-yyyy, that will not have any effect if the underlying dates are still not valid.

Note that the dates in both sheets must be valid for the formulas to work, I can not see if there are any problems with the dates in the summary report.

I would suggest that you revert to an unedited copy and try the text to columns fix again, all dates should convert to the correct format, either dd/mm/yyyy or mm/dd/yyyy depending on your system settings. If they don't then there is another underlying problem, this could be zero width characters if your data comes from a non excel source such as a web page.

Changing the format of the cells will not change how the formulas evaluate the dates, all valid dates are comparable regardless of format. Your formula will see 13/6/2020 (valid UK format), 6/13/20 (valid US format), 2020/06/13 (valid international format), or even variations of 13 June 2020 (in a locally valid format) as the same date, however a valid US date will not be recognised with UK settings and vise versa.

 

[jcgraham0007]

after playing around I don't think its the dates now you've fixed the text to columns, for example, if I change the two dates on the report 07-01-2020 then it pulls data through if I then revert to date one 06-30-2020 and 07-01-2020 it brings back zero. this suggests the sumif statement is not looking between both dates and fixing on one date, would you agree??

Thanks for your help, i would not have got this far without your input and insight

 

[jasonb75]

Have you checked that the dates in the summary report are valid?

From the screen captures I can't see what the row and column references in your formula refer to, it looks as if the formula in post 1 should go into F8, try this very slight edit to your formula then fill the table from there.

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

 

[jcgraham0007]

Hi Jason

Sorry I've not responded sooner been working on another project at work all day.

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.

What I need to do now is find a way of it bringing back the results by looking at any dates between the 2 date checks.

What I also need to do is find a way if possible of using the today() statement in the cell but getting the formula to see the date in the cell and pull a result not the formula today().