+ Reply to Thread
Results 1 to 3 of 3

SUMM if column header matches

  1. #1
    Registered User
    Join Date
    03-16-2012
    Location
    Russia
    MS-Off Ver
    Excel 2010
    Posts
    98

    SUMM if column header matches

    Hello!
    I can't figure out how to write a formula that will check for the condition not only rows, but also column headers (which I need to sum).
    Example in the attachment. J15 - my failed attempt to create a formula. K15 - what should be the correct result of the formula
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: SUMM if column header matches

    Please try

    =SUMPRODUCT(COUNTIFS(J3:J5,E17:G17,K3:K5,1)*COUNTIFS(J8:J9,A18:A42,K8:K9,1)*(B18:B42=J11)*(C18:C42=J12),E18:G42)
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,781

    Re: SUMM if column header matches

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    Selection criteria (yellow for manual input)
    2
    Select year
    Comment
    3
    Annual revenue year
    2020
    1
    1 = include in the calculation
    4
    2021
    0
    0 = exclude from the calculation
    5
    2022
    1
    6
    7
    Select year
    Comment
    8
    Project year
    2020
    1
    1 = include in the calculation
    9
    2021
    0
    0 = exclude from the calculation
    10
    11
    Customer
    Auchan
    12
    Manager
    Michael
    13
    14
    My effort (incorrect)
    Must be (correct)
    15
    Variant 1
    11,049
    21,575
    16
    Annual revenue
    17
    Project year
    Customer
    Manager
    Region
    2020
    2021
    2022
    24
    2020
    Auchan Michael Norh-West
    $5,539
    $7,957
    $5,263
    26
    2020
    Auchan Michael Norh-West
    $5,510
    $7,957
    $5,263
    45
    46
    $11,049
    10526



    K15=SUMPRODUCT((A18:A42=J8)*(B18:B42=J11)*(C18:C42=J12)*(ISNA(MATCH(E17:G17,J4,0)))*(E18:G42))


    Why k15 36701 ?

+ 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: 5
    Last Post: 01-30-2018, 07:32 AM
  2. [SOLVED] average whole column if header matches criteria
    By doylzer in forum Excel General
    Replies: 6
    Last Post: 09-22-2014, 08:20 AM
  3. [SOLVED] VLOOKUP returns column where column header matches another cell
    By bridge4444 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2014, 11:23 AM
  4. Replies: 1
    Last Post: 10-05-2013, 03:35 PM
  5. Get the column Header where value matches in multible columns
    By paddudommeti in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-30-2013, 03:36 AM
  6. lookup formula to return data where column header matches
    By tim-harrison in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-06-2013, 12:58 AM
  7. .Find header and column in another workbook and copy the cell that matches both
    By Ariadust in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2012, 12:15 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