Sumifs Issue in a daily spend file

[wat]

Hi Everyone,

Just a note this file looks rather complex but it is all using some version of sumifs (so I dont have to deal with pivot tables and updates easier). There are 3 tabs, only one contains all the formulas (Pacing Sheet). The other two tabs labels MTD Ad Group and PLA's are data files that I get directly from our data management platform.

My problem cells are highlighted in yellow and there are also notes in the file. Basically I am trying to use the sumifs using names ranges to return data that is between yesterday and the prior 7 days in one cell and yesterday and the prior 14 days in the other problem cells, and refer to a certain name with in a column. The named ranges are entire columns because they expand each day, the important named ranges are listed in the sheet. I have previously worked with sumifs and know how to apply the formula ex) =Sumifs(Impressions, Date, ">="&(Today()-7), Date, "<="&(Today()), Account Name, "*Business XYZ") of note instead of using Today() I reference a cell that has the formula in the file. The * are used before the text and makes it work similar to an advanced filter.

Can you please look at the yellow cells and help me get these sumifs to work? I think that the issue may be from the Date range in the MTD ad Group tab (the data comes in a funny format from the platform but I always just pate values) because I am able to get other sumifs to work when referring to only one date. Also the sumifs work when referring to the PLA tab. I would greatly appreciate any help! I have had others look at this as well and we cannot figure it out maybe we are missing something dumb. There are other problem cells but if we can just get the yellow ones to work I can figure out the rest.Pacing File Help.xlsb

PS I haven't gotten the file yet today for the MTD ad group so I adjusted the reporting dates in the sheet.

[mcmahobt]

A quick comment upon inspection: I'm assuming you are using wildcards in some of your SUMIFS...try implementing them like ..."*"&"pure"&"*"... and see what that returns.

[BadlySpelledBuoy]

The dates in named range "Date" are formatted as text which means your formula calculates as 0/0 which of course results in a DIV/0 error.

BSB

[wat]

In reference to the dates .. for example in the yesterday spend column I search then named range for the date of just 2/17/2014 and it returns the proper value. How would changing that format make a difference? I change the column to the date format and the formulas dont work still? However I did find something interesting after doing this when I click into a cell and press enter it shift from left to right orientation, but I dont want to do this thousands of times it would take forever.

[BadlySpelledBuoy]

but I dont want to do this thousands of times it would take forever.

You don't need to..
1) Highlight the column
2) Select "Data / Text To Columns / Next / Next / Date"
3) Chose the relevant date type (MDY?)
4) Click OK
5) Revel in the fact you've not had to edit each individual cell one after the other.

You could probably even record/write a macro to do that for all for you.

BSB

[mcmahobt]

Going off of BSB's post, you could try this in G11 to get around the date stored as text problem.

Please Login or Register  to view this content.

SUMPRODUCT is good for this since you don't have to enter it as an array formula (with your column referencing named ranges taken out).

However, SUMPRODUCT is not good with wildcards. SUMIFS is, but SUMIFS cannot accept DATEVALUE as a criteria range. This will leave you with using something along the lines of SUM(IF(... entered with CSE as an array formula.

[wat]

BSB thank you so much... I had actually did loop through this manually just for pure campaigns before refreshing the thread, ugh lol

Your help made the formulas work just as I need them to. Thank you so much, and the tip above was a lifesaver. I still have a lot tot learn haha. Problem solved though, PROPS

[wat]

mcmahobt Thanks for your time and suggestion using the sum product formula. I am not as familiar with them and will do more research and try to apply your idea as well after a little study. Thanks again for the help

WAT

[BadlySpelledBuoy]

No problem at all. We've all been there!

mcmahobt's approach works well too. I'm a big advocate of SUMPRODUCT as it's often a great lifesaver.

One other thing. You may want to consider dynamic named ranges to account for not having to manually adjust formulas when you add new data rather than referring to entire columns. In a complex workbook it can speed things up quite a bit.

BSB