+ Reply to Thread
Results 1 to 6 of 6

Sum of products meeting criteria on multiple rows matching other criteria w/o helper col?

  1. #1
    Registered User
    Join Date
    04-23-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    4

    Sum of products meeting criteria on multiple rows matching other criteria w/o helper col?

    I am having a challenging time wrapping my head around what I need to get the results I want without using the "Sums" helper column.

    My current formula is working on each row to arrive at the "Sums" and then adding the "Sums" column results if the line does not have an x in the "Excluded" column; this is the "Result:" The spreadsheet is attached, but is graphically represented below.


    Please Login or Register  to view this content.
    I would think I need an array or combination of SUMPRODUCT and SIMIF perhaps to avoid having the "Sums" column at all, but I just cannot seem to get my head around it. The goal would be to have columns A, B, C and D without E (Sums) on any spreadsheet and just have a full formula to get the same "Result:".

    I have been working on this for nearly a week to no avail. Any and all help would be greatly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,327

    Re: Sum of products meeting criteria on multiple rows matching other criteria w/o helper c

    array formula
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Formula in E is also could be more easy
    Please Login or Register  to view this content.
    Last edited by BMV; 04-23-2020 at 03:53 PM.

  3. #3
    Registered User
    Join Date
    04-23-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    4

    Re: Sum of products meeting criteria on multiple rows matching other criteria w/o helper c

    Thanks for the help! Those do look like easy formulas, however, neither of them work. They give error highlighting "x".

    I am using the clipboard copy from here to copy them into the spreadsheet. I thought perhaps it was the use of commas for periods in the 0,5 etc, but that did not change the error.

  4. #4
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,327

    Re: Sum of products meeting criteria on multiple rows matching other criteria w/o helper c

    Array formula - must finished by Ctrl+Shift+Enter
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-23-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    4

    Re: Sum of products meeting criteria on multiple rows matching other criteria w/o helper c

    BMV,

    Okay, it had to do with English vs international. Yet, once I fixed that, the result is #VALUE!, so there must be something more I am missing.

    Again, thanks for the help!

  6. #6
    Registered User
    Join Date
    04-23-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    4

    Re: Sum of products meeting criteria on multiple rows matching other criteria w/o helper c

    BMV,

    Aha - array formula, that worked.

    Thank you so much! Hope you are doing well with everything in the world - stay safe!

+ 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. Replies: 3
    Last Post: 12-23-2019, 10:02 AM
  2. VLOOKUP with multiple criteria - no helper column if possible
    By TRAPLETT in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 06-26-2017, 10:46 AM
  3. Sum multiple criteria with 0 to 3 cells helper only.
    By reggieneo in forum Excel General
    Replies: 4
    Last Post: 03-11-2017, 10:10 AM
  4. [SOLVED] Count Data Meeting Criteria Across Columns Meeting Criteria
    By idelta in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-06-2015, 03:53 PM
  5. [SOLVED] Display rows from multiple sheets matching a criteria
    By rudswa in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-17-2015, 02:07 PM
  6. Count rows meeting multiple criteria of multiple values
    By borcimaeh in forum Excel General
    Replies: 4
    Last Post: 08-24-2014, 02:37 PM
  7. Sorting and Delete rows meeting criteria based on sorting criteria
    By ron2k_1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2011, 02:40 PM

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