SUMIF function combined with VLOOKUP with many variables

[Liberty Prime]

Hi all!

I start my first post in this board with a question involving a vlookup function with three different variables I'm not able to make it work, in a context involving the use of AdWords and its expenses reports.

Here is the setup:

TAB 1: Here is the input raw data to be processed
TAB 2: Here is the output of the processed data

In the TAB 2 there is a column which should show the expenses according to the different accounts, based on the day of the month and country whose, values can be found in the TAB 1 (A for day, B for account, C for country and D for expenses). Until here, everything is fine.

The tricky thing I'm not able to solve is that in the TAB 2 there is a cell in which I manually type the day I want to check in the data from the TAB 1 (let's use C3 for instance), and the expenses should be summed from the first day of the month till the day specified in TAB2!C3 (included)

I don't know how to include a SUMIF or SUMIFS function to match this requirement in this Vlookup formula:

=iferror(vlookup($C$3&A5&B5,TAB1!$A:D,3,false),"-")

How can I order excel to perform this sum only if these values match?

Could you help me out with this please? Would be very helpful for the job I'm doing. I hope I was clear enough with the explanation, otherwise gladly will give more details.

Best regards!

 

[etaf]

what column do you want to sum

just use a SUMIFS ()

=SUMIFS(tab1!C:C,tab1!A:A,">="&DATE(YEAR(C3),MONTH(c3),DAY(1)),tab1!A:A,"<="&C3)

Where tab1! A: A has the dates
and tab1! C:C has the values you want to sum

 

[Liberty Prime]

Thanks a lot for your reply etaf!

Unfortunately I was not able to make the formula work... I think that the issue may have to do with the dates. Since it's not so easy to explain and I don't have the chance to share the google sheet, at least I have created a coupe of screenshots to be more specific. Hope it helps!

This is the Tab 1 (real name, Input)

The column A:A has the dates from column F:F with the format that google uses in their spreadsheets. B, C, and D were columns with different combinations of the column E:E and F:F I used with the vlookup, in order to achieve the result I didn't reach.

This one is the Tab 2 (real name, output). The cell A2 and B2 in yellow is retrieving the data from Input D3 and D4 (which also pulls the data from Input F5), and C2 in green is the day I would like to check how much has been spent from the 1st till the date in the cell. Lastly D2 contains the proper format of the date to be checked.

This value should be shown in the column D:D under MTD Spent, having as criteria: Country (A:A), if it's Main or Secondary account (B:B), and the date (either D2 or C2).

I hope that now everything makes a bit more sense.

Thanks again for the help.

 

[etaf]

if you wrote a sumifs()

how would you write it ?
ignore the cells you want to use to create the range

and then does the sumifs work

 

[Liberty Prime]

if you wrote a sumifs()

how would you write it ?
ignore the cells you want to use to create the range

and then does the sumifs work

I'm sorry to say that I didn't understand that clearly. I'm almost sure that the issue is caused by the format of the day.

In Output D6 (SUMIF for UK Main) I tried the following without sucess:

<bdo dir="ltr">=SUMIFS(Input!K:K,A17,B17,">="&12/1/2017,"<="&D2) (while 12/1/2017 won't work)

What I don't understand too is how can excel understand the values to be summed from Input if there is not a range in which it could check if the values match (like in Vlookup). That's why I was also considering using text with quotation marks for the criteria ("UK", "Main" etc), although question about the range question remains.</bdo>

 

[Liberty Prime]

Hum, somehow I can't edit my post, but another thing I thought (and also didn't work):

Check through vlookup if country and account from output match with the data from input. If true, analyze the date, sum if the day is equal or minor to the one given starting from the 1st day of the month.

Translated to formula I don't know how to make it. Something like this:

=SUMIFS(input!K:K, VLOOKUP(A7&B7,input!C:K,9,FALSE), DATE>= 12/01/17 & $D$2)

 

[etaf]

i was looking to try and understand how the formula should be constructed - before using any indirect of references other cells

=SUMIFS(Input!K:K,A17,B17,">="&12/1/2017,"<="&D2)

so
Input!K:K

is the range you want to SUM up
then we have various criteria - which will have to ALL apply - so like having an AND

next bit would be the range for the criteria and so needs to be a column - that will be looked down to match rows

so has to be the same sheet

input!A:A or where ever the dates are

thats why i was hoping you could just write the formula manually

lets say we wanted to look at a range of dates in column A between 2 dates and sum the corresponding values in column F

then

=SUMIFS(F:F,A:A,">="&DATEVALUE("1/1/17"),A:A,"<="&DATEVALUE("22/1/17"))

 

[Liberty Prime]

Such a mess Definitely I find this anything but easy to explain it with words. Way much easier if I can share a sheet. Check if the link works:

https://docs.google.com/spreadsheets/d/1gaBlVnH_7EjVf-l1d35BkrX9ObxJvpAEC20V45waAxo/edit?usp=sharing

In green in the the tab input, the values I want to analyze: date of the expense, account that registered the expense, value to sum based on the date starting from the first of this month until the last day (should be month to date).

In the tab output, in the column D should be written the output of such formula, but based on the cell C2 or D2.

I hope now finally can everything be better explained from my side, sorry.

 

[etaf]

i was still not sure on your spreadsheet

so rather than use one thats manipulating a lot of data - but use a sample thats close to what you want but minimise it

I have created one in dropbox in excel

it has a sheet called input
that has a column of dates A
a column for country
a column for brand and segment
a column with costs

now on output

I have put a date into cell D1 on the output sheet - which we will use to lookup the dates range from 1st of the month - to the date in cell D1

then created a little table with

country - brand/segment
and using sumifs - return the costs from input that meet that set of criteria

can we see if you can tell me if this is what you want to achieve on the other spreadsheet
and if so - what columns are used to sum and for every criteria

https://www.dropbox.com/s/7lmgr9r4ak5l669/lookup.xlsx?dl=0

there are lots of reasons why your sheet maynot work - as you are manipulating a lot of data and need to keep the dates as real dates and NOT text

also i'm in UK - so have to convert the dates to UK dates - but as they are text , thats not easy

 

[Liberty Prime]

Hi Etaf and happy new year

I'm very sorry for such delay in my answer, I had no internet access in a while.

What have you written in your last post worked just perfect and this thread can be considered/marked as solved. Thanks a lot!