+ Reply to Thread
Results 1 to 7 of 7

sumproduct/vlook up 3 variables

  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    58

    sumproduct/vlook up 3 variables

    Hi guys,

    I was wondering if you can help me with my formula

    I can't seem to pinpoint the mistake/error or perhaps in general regarding my formula.

    Can someone take a look at my spreadsheet.

    My goal is to basically, pull/extract data from another sheet to another sheet but it has to be meet criteria's

    =SUMPRODUCT(('3'!$C$2:$C$208=$B6)*('3'!$B$2:$B$208=E$4)*(TEXT('3'!$A$2:$A$208,"mmmm")=$D$3)) <--- I think this is wrong completely

    Eitherway,

    $B6 is the building name
    $D$3 is the month (which I can change that affects results of the month I want to see)

    I'm trying to get data from the amount of doors sold in each month when I create a table from sheet 3 but it has to follow each criteria due to amount of doors being sold in each building along with the month


    I have attached a work sample. I would be grateful if someone can help me out but also explain if possible

    I have tried using VLOOKUP for example =VLOOKUP(SheetnameColumn,TABLENAME,1,FALSE) but this only works when you have 1 month of data and when requiring to change month, It does not work
    Attached Files Attached Files
    Last edited by liqt; 02-03-2017 at 06:44 AM.

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: sumproduct/vlook up 3 variables

    As per your sheet 2 : 5 & 3 (in cell "N5" & "N6") is corrected.
    Look into your sheet1 : total number of door missing : 05 and Wrong colour 03
    how comes 04 each.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct/vlook up 3 variables

    Try:

    =SUMPRODUCT(('3'!$B$2:$B$20=$B6)*(TEXT('3'!$A$2:$A$20,"mmmm")=$D$3)*'3'!$C$2:$C$20)
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    11-07-2012
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: sumproduct/vlook up 3 variables

    Quote Originally Posted by avk View Post
    As per your sheet 2 : 5 & 3 (in cell "N5" & "N6") is corrected.
    Look into your sheet1 : total number of door missing : 05 and Wrong colour 03
    how comes 04 each.
    What do you mean sorry?

    The month in sheet 2 changes to correspond to the data from sheet 1.

    Eitherway, I'm asking for help on a different thing as posted on topic.

    Can someone help me please?

  5. #5
    Registered User
    Join Date
    11-07-2012
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: sumproduct/vlook up 3 variables

    Quote Originally Posted by Glenn Kennedy View Post
    Try:

    =SUMPRODUCT(('3'!$B$2:$B$20=$B6)*(TEXT('3'!$A$2:$A$20,"mmmm")=$D$3)*'3'!$C$2:$C$20)
    Works, thank you!

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct/vlook up 3 variables

    You're welcome!! thanks for the Rep.

  7. #7
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: sumproduct/vlook up 3 variables

    First point : You need both of answer is "4" is it correct.
    If, think there are three criteria you cover. (Month, color, problem), then enter formula in "N5" (For Month=Febuary / color=Red / Problem=Door missing)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then come to point for "Wrong colour" (This also you mentioned answer is "4")
    If apply same above criteria (Month, color, problem)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The answer is "1"

    But i come to know more about both answer is "4" : As i think you have calculate both month + Building + Problem
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Is it correct.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. SUMPRODUCT for multiple variables?
    By bronsoval in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-05-2014, 08:51 PM
  2. [SOLVED] Sumproduct with 2 variables in 2 columns
    By nynemout42 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-18-2012, 04:08 AM
  3. [SOLVED] Using SUMPRODUCT and different variables
    By highguyuk in forum Excel General
    Replies: 3
    Last Post: 07-20-2012, 09:15 AM
  4. Sumproduct: how to ignore blank variables
    By robotlust in forum Excel General
    Replies: 5
    Last Post: 05-17-2012, 11:02 PM
  5. Too many variables on sumproduct?
    By nataliarizzatti in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-31-2011, 07:54 PM
  6. sumproduct, 2 variables in 2 columns, is it possible?
    By Bucco63 in forum Excel General
    Replies: 5
    Last Post: 03-08-2010, 05:28 PM
  7. sumproduct - three variables
    By jcastellano in forum Excel General
    Replies: 10
    Last Post: 02-20-2008, 08:54 PM
  8. Sumproduct - four variables
    By jcastellano in forum Excel General
    Replies: 5
    Last Post: 09-05-2007, 11:16 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1