+ Reply to Thread
Results 1 to 8 of 8

Can I increment the 'criteria' part of SUMIF function across columns

  1. #1
    Registered User
    Join Date
    04-24-2015
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    3

    Can I increment the 'criteria' part of SUMIF function across columns

    Hi,

    First time poster.

    I am looking to increment the 'criteria' part of a SUMIF funtion across columns.

    I have a sheet with 3 columns with the following formula in it:

    =SUMIF(A:A,"1",C:C) =SUMIF(A:A,"2",C:C) =SUMIF(A:A,"3",C:C)
    =SUMIF(A:A,"1",D:D) =SUMIF(A:A,"2",D:D) =SUMIF(A:A,"3",D:D)
    =SUMIF(A:A,"1",E:E) =SUMIF(A:A,"2",E:E) =SUMIF(A:A,"3",E:E)
    =SUMIF(A:A,"1",F:F) =SUMIF(A:A,"2",F:F) =SUMIF(A:A,"3",F:F)
    =SUMIF(A:A,"1",G:G) =SUMIF(A:A,"2",G:G) =SUMIF(A:A,"3",G:G)
    =SUMIF(A:A,"1",H:H) =SUMIF(A:A,"2",H:H) =SUMIF(A:A,"3",H:H)
    =SUMIF(A:A,"1",I:I) =SUMIF(A:A,"2",I:I) =SUMIF(A:A,"3",I:I)
    =SUMIF(A:A,"1",J:J) =SUMIF(A:A,"2",J:J) =SUMIF(A:A,"3",J:J)
    =SUMIF(A:A,"1",K:K) =SUMIF(A:A,"2",K:K) =SUMIF(A:A,"3",K:K)
    =SUMIF(A:A,"1",L:L) =SUMIF(A:A,"2",L:L) =SUMIF(A:A,"3",L:L)
    =SUMIF(A:A,"1",M:M) =SUMIF(A:A,"2",M:M) =SUMIF(A:A,"3",M:M)
    =SUMIF(A:A,"1",N:N) =SUMIF(A:A,"2",N:N) =SUMIF(A:A,"3",N:N)
    =SUMIF(A:A,"1",O:O) =SUMIF(A:A,"2",O:O) =SUMIF(A:A,"3",O:O)
    =SUMIF(A:A,"1",P:P) =SUMIF(A:A,"2",P:P) =SUMIF(A:A,"3",P:P)
    =SUMIF(A:A,"1",Q:Q) =SUMIF(A:A,"2",Q:Q) =SUMIF(A:A,"3",Q:Q)
    =SUMIF(A:A,"1",R:R) =SUMIF(A:A,"2",R:R) =SUMIF(A:A,"3",R:R)
    =SUMIF(A:A,"1",S:S) =SUMIF(A:A,"2",S:S) =SUMIF(A:A,"3",S:S)
    =SUMIF(A:A,"1",T:T) =SUMIF(A:A,"2",T:T) =SUMIF(A:A,"3",T:T)
    =SUMIF(A:A,"1",U:U) =SUMIF(A:A,"2",U:U) =SUMIF(A:A,"3",U:U)
    =SUMIF(A:A,"1",V:V) =SUMIF(A:A,"2",V:V) =SUMIF(A:A,"3",V:V)
    =SUMIF(A:A,"1",W:W) =SUMIF(A:A,"2",W:W) =SUMIF(A:A,"3",W:W)
    =SUMIF(A:A,"1",X:X) =SUMIF(A:A,"2",X:X) =SUMIF(A:A,"3",X:X)
    =SUMIF(A:A,"1",Y:Y) =SUMIF(A:A,"2",Y:Y) =SUMIF(A:A,"3",Y:Y)
    =SUM(D2:D24) =SUM(E2:E24) =SUM(F2:F24)

    1. I need to add further columns which have the criteria in the SUMIF function incrementing by 1 - is there a quick way to do this. The next column I am looking for would start =SUMIF(A:A,"3",C:C)
    2. Also the SUM function at the bottom needs to increment by Column. The next column I am looking for would be =SUM(G2:G24)

    Thanks

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Can I increment the 'criteria' part of SUMIF function across columns

    Use this and drag to the right as far as you need
    =SUMIF($A:$A,COLUMNS($A:A),$C:$C) (it will start from "1").
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Can I increment the 'criteria' part of SUMIF function across columns

    Please attach sample file - remove if you have any sensitive dataAttach A File.jpg
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Can I increment the 'criteria' part of SUMIF function across columns

    Indeed, you can make the whole process much easier if you use this. Drag across and down.
    =SUMIF($A:$A,COLUMNS($A:A),OFFSET($C:$C,,ROWS($1:1)-1))

    This will do two things. Increase the criterion by 1 every column you drag it to the right AND increase the column letter that tou are summing by one (C:C to D:D, to E:E, etc) for every row that you drag it down.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Can I increment the 'criteria' part of SUMIF function across columns

    It'll maybe be easier to follow on this sheet.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-24-2015
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    3

    Re: Can I increment the 'criteria' part of SUMIF function across columns

    It Works! Thank you so much Glenn - you are a genius!

  7. #7
    Registered User
    Join Date
    04-24-2015
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    3

    Re: Can I increment the 'criteria' part of SUMIF function across columns

    It Works! Thank you so much Glenn - you are a genius!

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Can I increment the 'criteria' part of SUMIF function across columns

    Thanks for the Reputation that you have added. It's much appreciated. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. Using SUMIF with a keyword as part of the criteria
    By cdeguzman in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-27-2014, 10:30 AM
  2. Replies: 0
    Last Post: 05-11-2012, 11:51 AM
  3. Increment Rows Across Columns in copied SUMIF formula??
    By Chizilla in forum Excel General
    Replies: 3
    Last Post: 02-10-2010, 05:56 PM
  4. SUMIF Using Criteria From Two Columns
    By Karleajensar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2007, 01:04 PM
  5. Sumif function with two criteria from different columns
    By SamFortMyers in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-24-2005, 08:06 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