Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: SUMIF with a range as Criteria

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to do a SUMIF, except instead of using a value as criteria, i need to compare to a range of cells, and if it matches any of those cells, then I want it to be summed. I'm guessing if this can be done it would be an array formula of some kind, but I've been unable to make it work so far.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I put the numbers in the criteria range in B1:B5 and the numbers ot be added in D1:D5.

    The following array formula seems to work:
    =SUM(SUMIF(D1:D5,$B$1:$B$5,D1:D5))
    (Be sure to hit control-shirt-enter instead of enter upon input.)

    You can use a 2-D range in place of either $b$1:$b$5 or D1:D5.
    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Now, suppose I ALSO want $c$1:$c$5 to be equal to "c" in order for it to sum, how would I incorporate that into your suggested formula?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,604
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-27 12:36, tdh222 wrote:
    Now, suppose I ALSO want $c$1:$c$5 to be equal to "c" in order for it to sum, how would I incorporate that into your suggested formula?
    What about:

    =SUMPRODUCT((ISNUMBER(MATCH(crit-range,Range1,0)))*(Range2="c"),Range2)

    where Range1 is tested whether it meets any of the conditions in crit-range, Range2 is tested whether it has "c", and Range2 is summed.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •