+ Reply to Thread
Results 1 to 7 of 7

Regular formula or array for multi colum multi criteria?

  1. #1
    Valued Forum Contributor scaffdog845's Avatar
    Join Date
    02-01-2008
    Location
    Aston, PA. USA.
    MS-Off Ver
    Microsoft 365
    Posts
    360

    Regular formula or array for multi colum multi criteria?

    Hey gang

    In the attached spreadsheet I have an example of a time entry setup in A7:F15. The source workbook goes from A7:F66. I'm looking for a formula that would give me a sum of the charges entered for the day. It's easy enough to use a helper column and sum it up like I have in the example in H7:H16 but I was hoping for a formula or array formula that could be put at the bottom of F:F to calculate the totals without the helper.

    Thanks
    Attached Files Attached Files
    Last edited by scaffdog845; 03-25-2016 at 01:31 PM.
    Click here to read the Forum Rules
    Whatever it is in life you decide to go after, go after with great ferocity.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Regular formula or array for multi colum multi criteria?

    If you just need a total

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Regular formula or array for multi colum multi criteria?

    Try

    =SUMPRODUCT(F7:F15,C7:C15)

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,820

    Re: Regular formula or array for multi colum multi criteria?

    Try in F16

    =SUMPRODUCT(($C$7:$C$15)*($F$7:$F$15))

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Regular formula or array for multi colum multi criteria?

    I won't be repeating the same formula.

    On a side note...

    This was the original intended use for SUMPRODUCT. My, how its use has morphed over the years!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Valued Forum Contributor scaffdog845's Avatar
    Join Date
    02-01-2008
    Location
    Aston, PA. USA.
    MS-Off Ver
    Microsoft 365
    Posts
    360

    Re: Regular formula or array for multi colum multi criteria?

    Must be a Friday because I was over thinking that one to the extreme! Thanks guys

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Regular formula or array for multi colum multi criteria?

    You're welcome.

    FYI, the difference between the 2 methods is subtle, but significant.

    =SUMPRODUCT(C7:C15*F7:F15)
    =SUMPRODUCT(C7:C15,F7:F15)

    With the (C7:C15*F7:F15) syntax, sumproduct isn't actually doing the product part (C7*F7 and C8*F8 etc)
    Instead, that is being done internally, and the results of each product are fed to sumproduct as an array, like
    =SUMPRODUCT({200,188,423,etc})
    Then the only thing sumproduct is actually doing is the SUM part.


    With the (C7:C15,F7:F15) syntax, sumproduct is actually doing ALL the work.


    This isn't really a big deal, and I don't know if there is any performance impact.
    However, the first method (C7:C15*F7:F15) would result in #Value! Error if there are any TEXT values within either range.
    But the 2nd method (C7:C15,F7:F15) would simply ignore those text entries and continue with the rest of the calculation.

+ 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. New & Lost: Multi Criteria & Multi Row INDEX, SMALL, MATCH...
    By morleyp in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-23-2014, 12:40 PM
  2. Large multi criteria / Vlookup/Choose Multi criteria
    By deanusa in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-20-2014, 01:48 AM
  3. SUMIFS for Multi set of colums & multi criteria
    By Harish Kumar M in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-02-2014, 10:11 PM
  4. Count it multi criteria accross multi cells
    By jfoley5197 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-10-2013, 03:42 PM
  5. Sum if array formula, with multi criteria, some in the same column
    By carlosmaldonado in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-12-2012, 04:30 AM
  6. Multi-criteria Counting (array formulas)
    By CWIS in forum Excel General
    Replies: 4
    Last Post: 12-15-2009, 03:48 PM
  7. Multi Criteria combo box returning array values
    By robcosta in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-24-2008, 10:41 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