+ Reply to Thread
Results 1 to 18 of 18

SUMIF Criteria range to be taken automatically

  1. #1
    Forum Contributor
    Join Date
    08-02-2015
    Location
    Hyderabad
    MS-Off Ver
    2007
    Posts
    160

    SUMIF Criteria range to be taken automatically

    Dear All,

    Actually SUMIF function will be like SUMIF(Range, Criteria, SUM Range)

    SO here I want range (in the sumif function) to be taken automatically rather than me selecting the particular range based on header

    Ex: I'm using formula =SUMIF(Production!$A$37:$AG$45,Export!A2,Production!$BL$38:$BL$45) where as it is returning zero even there is a value for that particular item

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: SUMIF Criteria range to be taken automatically

    Criteria range and Sum range must have same length. Numbers may be in text format.

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMIF Criteria range to be taken automatically

    Quote Originally Posted by kvsrinivasamurthy View Post
    Criteria range and Sum range must have same length.
    Not necessarily...

    Data Range
    A
    B
    C
    D
    1
    X
    10
    30
    2
    Z
    10
    3
    X
    10
    4
    Y
    10
    5
    X
    10
    6
    ------
    ------
    ------
    ------


    This formula entered in D1:

    =SUMIF(A1:A5,"X",B1)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Contributor
    Join Date
    08-02-2015
    Location
    Hyderabad
    MS-Off Ver
    2007
    Posts
    160

    Re: SUMIF Criteria range to be taken automatically

    Quote Originally Posted by Tony Valko View Post
    Not necessarily...

    Data Range
    A
    B
    C
    D
    1
    X
    10
    30
    2
    Z
    10
    3
    X
    10
    4
    Y
    10
    5
    X
    10
    6
    ------
    ------
    ------
    ------


    This formula entered in D1:

    =SUMIF(A1:A5,"X",B1)
    But as per my formula it is becoming zero, I dont know why is it happening? Please clarify

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUMIF Criteria range to be taken automatically

    Quote Originally Posted by laansesu View Post
    Ex: I'm using formula =SUMIF(Production!$A$37:$AG$45,Export!A2,Production!$BL$38:$BL$45) where as it is returning zero even there is a value for that particular item
    Why the difference?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Forum Contributor
    Join Date
    08-02-2015
    Location
    Hyderabad
    MS-Off Ver
    2007
    Posts
    160

    Re: SUMIF Criteria range to be taken automatically

    Sorry I think I even considered Header also. After seeing your comment I've changed it to $A$38 where it is $A$37 but still ans is zero. I'm still not getting the accurate answer. Request you to please clarrify.

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUMIF Criteria range to be taken automatically

    Suggest you upload your workbook.

    Regards

  8. #8
    Forum Contributor
    Join Date
    08-02-2015
    Location
    Hyderabad
    MS-Off Ver
    2007
    Posts
    160

    Re: SUMIF Criteria range to be taken automatically

    PFA file and do the needful.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF Criteria range to be taken automatically

    Try ..

    =SUMIF(Production!$B$38:$B$45,Export!A2,Production!$BL$38:$BL$45)

    SO here I want range (in the sumif function) to be taken automatically rather than me selecting the particular range based on header
    Not sure what you mean by the above

  10. #10
    Forum Contributor
    Join Date
    08-02-2015
    Location
    Hyderabad
    MS-Off Ver
    2007
    Posts
    160

    Re: SUMIF Criteria range to be taken automatically

    Here what you are doing is, you are selecting the particular row (of Downrod i.e., B:B) where as I'm selecting all the rows (i.e., from A:AG) also so that I can use only one formula for populating the data in this present sheet

    Can we do like that. Please confirm.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF Criteria range to be taken automatically

    try

    =SUM(IF(Production!$A$38:$AG$45=Export!A2,Production!$BL$38:$BL$45))

    Enter with Ctrl+Shift+Enter

    You need to remove #N/A errors by putting IFERROR additions around your formulae for "Closing Stock"

    =IFERROR(your formula,"")

  12. #12
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUMIF Criteria range to be taken automatically

    Quote Originally Posted by JohnTopley View Post
    =SUM(IF(Production!$A$38:$AG$45=Export!A2,Production!$BL$38:$BL$45))
    Just curious - what advantage does that have over the equivalent SUMIF construction?

    Regards

  13. #13
    Forum Contributor
    Join Date
    08-02-2015
    Location
    Hyderabad
    MS-Off Ver
    2007
    Posts
    160

    Re: SUMIF Criteria range to be taken automatically

    Quote Originally Posted by JohnTopley View Post
    try

    =SUM(IF(Production!$A$38:$AG$45=Export!A2,Production!$BL$38:$BL$45))

    Enter with Ctrl+Shift+Enter

    You need to remove #N/A errors by putting IFERROR additions around your formulae for "Closing Stock"

    =IFERROR(your formula,"")
    Thanks its working now!!!!
    Last edited by laansesu; 11-17-2015 at 07:29 AM.

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF Criteria range to be taken automatically

    @XOR LX

    SUMIF (for me) did not work over a range i.e range which was not single column or single row: maybe I was doing something wrong?

    Original SUMIF was

    =SUMIF(Production!$A$38:$AG$45,Export!A2,Production!$BL$38:$BL$45))

    @ Iaansesu

    The #N/A are displaying because you have many #N/As in the data range.

    I removed all the #N/A errors and retrieved the value 1000.

  15. #15
    Forum Contributor
    Join Date
    08-02-2015
    Location
    Hyderabad
    MS-Off Ver
    2007
    Posts
    160

    Re: SUMIF Criteria range to be taken automatically

    Yes I got it!!! I've gone through the evaluation formula to check the issue. Now its working..I also changed this post as solved now.

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF Criteria range to be taken automatically

    See attached.
    Attached Files Attached Files

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF Criteria range to be taken automatically

    Glad it is solved!

  18. #18
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUMIF Criteria range to be taken automatically

    Quote Originally Posted by JohnTopley View Post
    @XOR LX

    SUMIF (for me) did not work over a range i.e range which was not single column or single row: maybe I was doing something wrong?
    Apologies. You're perfectly correct. I didn't read the question properly.

    Regards

+ 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] Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2014, 09:25 PM
  2. SUMIF when criteria is outside of the range
    By jdohlinger in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-28-2012, 04:49 PM
  3. Excel 2008 : SUMIF using Name and Date Range criteria
    By cloudydaysong in forum Excel General
    Replies: 8
    Last Post: 10-19-2010, 09:06 PM
  4. SumIf using date criteria range and one other criteria
    By DJTMONEY in forum Excel General
    Replies: 1
    Last Post: 04-20-2010, 04:10 PM
  5. SUMIF with AND for range and criteria
    By davidm_ba in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 09-06-2005, 07:05 PM
  6. SUMIF using two columns in both Range and Criteria
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-06-2005, 12:05 PM
  7. [SOLVED] sumif when criteria is a range
    By jeremy via OfficeKB.com in forum Excel General
    Replies: 7
    Last Post: 08-15-2005, 01:05 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