+ Reply to Thread
Results 1 to 4 of 4

Formula in SUMIF Criteria

  1. #1
    Registered User
    Join Date
    04-06-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Formula in SUMIF Criteria

    Hello,

    Thanks for your help in advance!

    Let's say, that in row A1:A10 you have numbers from 1 to 10
    In Cell B1, you calculate average of the Range A1:A10
    In Cell C1, you would like to SUM all the numbers from Range A1:A10 (this is a evaluation and sum range, to be more exact) that are greater then average value in B1!
    Formula =sumif(A1:A10;">B1";A1:A10) is not working, since Excel "seas" the formula in Criteria field with "" meaning, that Criteria searches B1 or even >B1 as a string in A1:A10 range.
    If Instead of B1, I enter a value, which is a result displayed in B1 Cell, than it works fine (eg: =sumif(A1:A10;">4,52";A1:A10)) but this is not a solution, since the value in B1 could be dynamic and I don't want to change sumif formula every time, value in Cell B1 in changed.

    BR

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,601

    Re: Formula in SUMIF Criteria

    Pull B1 out of quotes:

    B1 is a value in cell B1
    "B1" is string B1

    =SUMIF(A1:A10;">"&B1)

  3. #3
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Formula in SUMIF Criteria

    Hi Zvity,

    Try:

    =SUMIF(A1:A10;">"&B1)

    You don't need to repeat A1:A10 if it's both the criteria range and sum range.

  4. #4
    Registered User
    Join Date
    04-06-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Formula in SUMIF Criteria

    Thank's, that works ... (Hvala Zbor!)

+ 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