+ Reply to Thread
Results 1 to 6 of 6

countif or sumproduct help

  1. #1
    Registered User
    Join Date
    12-19-2008
    Location
    vancouver
    Posts
    2

    countif or sumproduct help

    Hello I would like to display in the respected columns DEF (table) the total number of times assist, escelation, & other were used per name. I.e. see table. Also I will have 50-100 different Names, repeating various times with different Types. Thank you.

    A B C D E F
    Name Type Name Assist Escelation Other
    Sara assist Sara result formula result formula result formula
    Tim escelation Tim 2 1 0
    Joe assist Joe
    Tom assist Tom
    Sara escelation Jane
    Tim assist
    Sara assist
    Jane other
    Tim assist

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    In cell D2, place the following formula and copy down and over:

    =SUMPRODUCT(($A$2:$A$10=$C2)*($B$2:$B$10=D$1))

  3. #3
    Forum Contributor
    Join Date
    08-31-2007
    Location
    SW Ireland
    Posts
    323
    Depending on the layout of your data file, this might be achievable using a Pivot table.
    Alternatively maybe the COUNTIF() function might be usable. Equally, the SUMPRODUCT() function could be a possibility.

    If you could upload a copy of your file you'd probably get a more precise answer.

  4. #4
    Registered User
    Join Date
    12-19-2008
    Location
    vancouver
    Posts
    2
    Yah sorry didnt see attachment here is a sample sheet:

    Book1.xls

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    E3 and copy across and down, =SUMPRODUCT( ($A$2:$A$20 = $D3) * ($B$2:$B$20 = E$2) )
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    re: countif or sumproduct help

    There is more than one way, but for the table you've created simply use this:

    =SUMPRODUCT(--($A$3:$A$16=$D3),--($B$3:$B$16=E$2))
    Attached Files Attached Files
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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