Page 2 of 2 FirstFirst 1 2
Results 11 to 14 of 14

Thread: Index/match with multiple match criteria

  1. #11
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,840
    Articles
    0
    Excel Version
    365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Quote Originally Posted by USAOz View Post
    I wish to sum the total wages for a selected employee in a particular quarter.
    Attached has some formulae on the Wages & Salaries sheet in range D31:G35.
    Select your employee ID in cell A32.
    Have I guessed right?
    Attached Files Attached Files
    Last edited by p45cal; 2020-11-20 at 02:23 PM.

  2. #12
    Seeker USAOz's Avatar
    Join Date
    Sep 2017
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by Bob Phillips View Post
    Here is a formula to give you weekly income for Q1, the rest should be easily figured out

    =SUMPRODUCT(--(ROUNDUP(MONTH($C$8:$C$30)/3,0)=E$30),--($C$8:$C$30<>""),$L$8:$L$30)
    To which thread is this related? Your cell references do not match anything in my last file upload.
    Also, are the double minuses in your formula correct? I can't work out the logic.
    Neverheless, thanks for taking the time to attempt a solution. It is greatly appreciated.

  3. #13
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,518
    Articles
    0
    Excel Version
    Office 365 Subscription
    The double minus (--) forces what follows to be a numerical value instead of text, so yes, it is correct.
    Ali
    Enthusiastic self-taught user of MS Excel!

  4. #14
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,834
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by USAOz View Post
    To which thread is this related? Your cell references do not match anything in my last file upload.
    Also, are the double minuses in your formula correct? I can't work out the logic.
    Neverheless, thanks for taking the time to attempt a solution. It is greatly appreciated.
    When I said Q1, I meant Quarter 1, cell E31, not cell Q1.

    It most definitely does match your last upload, C8:C30 is the date which I parse, L8:L309 is the weekly income.

    The double unary is to coerce an array of TRUE/FALSE into an array of 1/0 so that the product part of SUMPRODUCT has numbers to work on (a non-matched condition, FALSE, evaluates to 0 and so the product adds in 0).

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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