+ Reply to Thread
Results 1 to 6 of 6

Count IF Formula Assistance- Adding all values in a row for a specific region.

  1. #1
    Registered User
    Join Date
    10-26-2016
    Location
    Vancouver
    MS-Off Ver
    2013
    Posts
    10

    Count IF Formula Assistance- Adding all values in a row for a specific region.

    I have an excel document that shows Partners, Regions and investment assets among other data.

    Column B has a list of 100 partners, they come from 6 different regions and those regions are in column C : AB,BC,SK,MB,ON,ATL. Column D has the partners asset size listed.

    I need to create a formula in a pivot table that can add all the regions together for their total asset size

    So each region has a cell and in the adjacent cell would be the total assets.

    Thank you

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Count IF Formula Assistance- Adding all values in a row for a specific region.

    Can you please attach a small mock-up and the expected results for us to help give you an appropriate response.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    10-26-2016
    Location
    Vancouver
    MS-Off Ver
    2013
    Posts
    10

    Re: Count IF Formula Assistance- Adding all values in a row for a specific region.

    Please see attachments for more details.

    Thank you
    Attached Files Attached Files

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Count IF Formula Assistance- Adding all values in a row for a specific region.

    J5 =SUMIFS($C$2:$C$25,$B$2:$B$25,$I4,$F$2:$F$25,J$3) >> copied across to K5 and down

    J13 =SUMIFS($D$2:$D$25,$B$2:$B$25,$I4,$F$2:$F$25,J$3) >> copied across to K13 and down

    In column L, just sum up column J and K

    Note: You could just as easily use a pivot table also...

    Note: Here's another option in the attachment. Change the drop down in J2.
    Attached Files Attached Files
    Last edited by jeffreybrown; 04-07-2017 at 07:25 PM.

  5. #5
    Registered User
    Join Date
    10-26-2016
    Location
    Vancouver
    MS-Off Ver
    2013
    Posts
    10

    Re: Count IF Formula Assistance- Adding all values in a row for a specific region.

    I'm sorry but I forgot to mention that this data is held on another sheet called Data. The data is in the same cells as the example. How would I go about doing this in another sheet.

    Thank you.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Count IF Formula Assistance- Adding all values in a row for a specific region.

    In the example posted in #4, just reference the sheet where the data resides.

    =SUMIFS(INDEX(Data!$C$2:$D$25,,MATCH($J$2,Data!$C$1:$D$1,0)),Data!$B$2:$B$25,$I4,Data!$F$2:$F$25,J$3)

+ 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. [SOLVED] Formula to put differnt contries for their specific regions, ATTCD countries with region
    By raysrains in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-28-2014, 09:42 AM
  2. Replies: 1
    Last Post: 03-29-2014, 07:00 AM
  3. Assistance in Sum/CountA formula to only count 1 of duplicate values
    By Excelhandicap in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2013, 09:06 PM
  4. Formula for adding specific values based on data in other columns
    By Adelade in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2013, 05:15 AM
  5. Count rows in specific region
    By Lv27 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-18-2012, 09:02 AM
  6. formula based on region and less than certain values
    By djblois1 in forum Excel General
    Replies: 1
    Last Post: 05-05-2012, 08:37 PM
  7. Adding To Formula To Count Values
    By Karleajensar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-06-2007, 12:59 PM

Tags for this Thread

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