• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup/Indexmatch with 3 conditions

M

Maverick

New Member
Hi Friends,

From a data table, I am looking to pull the sum of the following fields: Year, SalesHouse, Demograph and Month. I've tried Vlookups, Sumifs and Index match but failed.

Please could you take a look at the attached and advise?
(The box in yellow is what I am looking to pull with results)

Many Thanks
 

Attachments

  • Commercial Impacts.xlsx
    55.2 KB · Views: 0
Syedali, may I ask why you added a helper column when you had that data already on column C?

That aside I had the same solution.
 
Tiago MF said:
Syedali, may I ask why you added a helper column when you had that data already on column C?

That aside I had the same solution.
Click to expand...
There is no Unique records in column c so i use helper column.
For that only you also made grouping sheet...Is that correct.

Edit: If it is wrong kindly give me an example . What is your requirement.
 
Wow - im staggered by the speed of the replies!
Thank you so much - I spent such a long time trying so really appreciate the help.
SyedAli, the helper column or Column C are unique to the channels but I thought you did it for my ease as your formula is more precise than my long one!

Many Thanks once again!
 
Maverick said:
Wow - im staggered by the speed of the replies!
Thank you so much - I spent such a long time trying so really appreciate the help.
SyedAli, the helper column or Column C are unique to the channels but I thought you did it for my ease as your formula is more precise than my long one!

Many Thanks once again!
Click to expand...
You got your answer or not. If it is really helpful to you then don't forget to like.
 
Hi Maverick,

You can also try below formula in O19 and copy down and across:

=SUMPRODUCT(INDEX($F$4:$K$450,,MATCH($N$17,$F$2:$K$2,0))*($B$4:$B$450=$N$18)*($D$4:$D$450=$N19)*(O$18=$C$4:$C$450))

Regards,
 
Hi,

Sorry, me again.

I've adapted my spreadsheet with the kind advice above but tried (and failed) to adapt further.

I have total data by the Saleshouse, but I would like to work out share by the channel itself (channel/total TV impacts in market).

I've managed to create a 'Kids Share' using the help already provided but unsure how to adapt the formula for the changing month/year.

As per the red cell (e4), I require the last division of Z5 to take into account the new month(s) as will not work for the next month (Feb).....

Please see attached.

Thanks
 

Attachments

  • Commercial Impacts.xlsx
    113.1 KB · Views: 0
Maverick said:
Hi,

Sorry, me again.

I've adapted my spreadsheet with the kind advice above but tried (and failed) to adapt further.

I have total data by the Saleshouse, but I would like to work out share by the channel itself (channel/total TV impacts in market).

I've managed to create a 'Kids Share' using the help already provided but unsure how to adapt the formula for the changing month/year.

As per the red cell (e4), I require the last division of Z5 to take into account the new month(s) as will not work for the next month (Feb).....

Please see attached.

Thanks
Click to expand...
 

Attachments

  • Copy of Commercial Impacts.xlsx
    137.8 KB · Views: 0
Hi Jayalaxmi,
Thanks for looking at this but the situation is the same-I have the same output (red cell) in my original sheet but its the last bit that needs to change (Z5) so that month in column J references the correct month in column Z..
Currently, they are all referencing only January 2014 (Z5)!
 
ie E28 is Feb 2014 so should reference Z6 and not Z5 (Jan). Im finding it hard to reference the correct month in column Z ..
 
You must log in or register to reply here.
Back
Top