+ Reply to Thread
Results 1 to 6 of 6

SUMIF across several columns

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    SUMIF across several columns

    The attached work book shows columns of numbers which can be either from "a" or "b". I use a SUMIF formula for each column to add the figures for "a" and then sum these to get the total number for the whole table
    It occurs to me that by using a formula like SUMIF($E:$E,"a",$F:$L) I could do this in one go. Unfortunately this formula does not work, so I would be very grateful for one that does.
    John
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,732

    Re: SUMIF across several columns

    Hi,
    How about using this array formula?"

    =SUM(IF($E$8:$E$15="a",(F8:L15)))

    **Array formula should be conformed with CTRL+SHIFT+ENTER


    OR using sumproduct:
    =SUMPRODUCT(($E$8:$E$15="A")*($F$8:$L$15))

  3. #3
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: SUMIF across several columns

    belinda200,
    Thank you very much for your interest and help. I couldn't get the sumproduct to work when transferred to my real data, but the array formula worked very well .
    The time taken to calculate is very noticeable as compared to my original method. There are 50+ columns and about 3000 rows in the set of which about 15% are selected.
    John

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,732

    Re: SUMIF across several columns

    The sumproduct should be easier to calculate. why cant you apply that on your original file?

    I think it is worth the time and effort checking why it doesn't work for you, and fix that, so that your file will not crash every time you open it....
    Last edited by belinda200; 09-29-2020 at 09:39 AM.

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,009

    Re: SUMIF across several columns

    I agree Belinda, or perhaps creating totals in the next blank column and using a simple sumif

    It is also quicker if the ranges are their actual length, selecting a full column has an adverse effect on the calculation speed, as it is looking at redundant cells
    Last edited by davsth; 09-29-2020 at 09:42 AM.

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,732

    Re: SUMIF across several columns

    I edited my above post....
    Can you upload a sample of your original file? I will see why the sumproduct isn't working well.....

+ 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] SUMIF last n columns YTD
    By ajw089 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-24-2019, 04:15 PM
  2. Sumif Help - two columns
    By Sama786 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-11-2019, 10:14 AM
  3. Sumif across columns
    By jw01 in forum Excel General
    Replies: 8
    Last Post: 06-29-2017, 02:26 PM
  4. sumif with not columns far apart
    By makinmomb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-05-2014, 04:24 PM
  5. [SOLVED] Sumif two columns matching another two columns
    By kl99ny in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-11-2013, 02:08 PM
  6. SumIf across columns
    By Lithium78 in forum Excel General
    Replies: 12
    Last Post: 02-19-2010, 04:01 AM
  7. Sumif across columns
    By mminsf in forum Excel Formulas & Functions
    Replies: 52
    Last Post: 09-06-2005, 12:05 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