If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sumif with or
I'm trying to do this:
=SUMIF(B5:B12,OR("=D*","=?D*"),D512) It returns zero each time. (BTW, =SUMIF(B5:B12,"=D*",D512) and =SUMIF(B5:B12,"=?D*",D512) both work just fine and return non zero sums.) So my question is, Where do I put the OR, or is there another way to do this? |
#2
|
|||
|
|||
I've tried and never been able to find a better solution than simply using:
=SUMIF(B5:B12,"=D*",D512)+SUMIF(B5:B12,"=?D*",D5 12) "C Glenn" wrote: I'm trying to do this: =SUMIF(B5:B12,OR("=D*","=?D*"),D512) It returns zero each time. (BTW, =SUMIF(B5:B12,"=D*",D512) and =SUMIF(B5:B12,"=?D*",D512) both work just fine and return non zero sums.) So my question is, Where do I put the OR, or is there another way to do this? |
#3
|
|||
|
|||
=SUM(SUMIF(B5:B12,{"D*","?D*"},D512))
C Glenn wrote: I'm trying to do this: =SUMIF(B5:B12,OR("=D*","=?D*"),D512) It returns zero each time. (BTW, =SUMIF(B5:B12,"=D*",D512) and =SUMIF(B5:B12,"=?D*",D512) both work just fine and return non zero sums.) So my question is, Where do I put the OR, or is there another way to do this? |
#4
|
|||
|
|||
Aladin,
Excellent. I have never seen that use of the Sumif before where it returns an array of values. Alok "Aladin Akyurek" wrote: =SUM(SUMIF(B5:B12,{"D*","?D*"},D512)) C Glenn wrote: I'm trying to do this: =SUMIF(B5:B12,OR("=D*","=?D*"),D512) It returns zero each time. (BTW, =SUMIF(B5:B12,"=D*",D512) and =SUMIF(B5:B12,"=?D*",D512) both work just fine and return non zero sums.) So my question is, Where do I put the OR, or is there another way to do this? |
#5
|
|||
|
|||
Somewhat like an implicit OR but it's actually an implicity IN! I'm
wondering if there are any other possibilities, i.e.: 250, 125. This doesn't work with the curlies. Thanks. Aladin Akyurek wrote: =SUM(SUMIF(B5:B12,{"D*","?D*"},D512)) C Glenn wrote: I'm trying to do this: =SUMIF(B5:B12,OR("=D*","=?D*"),D512) It returns zero each time. (BTW, =SUMIF(B5:B12,"=D*",D512) and =SUMIF(B5:B12,"=?D*",D512) both work just fine and return non zero sums.) So my question is, Where do I put the OR, or is there another way to do this? |
#6
|
|||
|
|||
Glenn,
This does work but you have to enclose them in quotes. {"100","250"} Alok "C Glenn" wrote: Somewhat like an implicit OR but it's actually an implicity IN! I'm wondering if there are any other possibilities, i.e.: 250, 125. This doesn't work with the curlies. Thanks. Aladin Akyurek wrote: =SUM(SUMIF(B5:B12,{"D*","?D*"},D512)) C Glenn wrote: I'm trying to do this: =SUMIF(B5:B12,OR("=D*","=?D*"),D512) It returns zero each time. (BTW, =SUMIF(B5:B12,"=D*",D512) and =SUMIF(B5:B12,"=?D*",D512) both work just fine and return non zero sums.) So my question is, Where do I put the OR, or is there another way to do this? |
#7
|
|||
|
|||
I can't get this to work. In every case, the first criteria is applied
and the second is ignored. Put these numbers in D, starting at D5: 45 123 789 4156 123 456 987 321 Now put these in G, starting at G5: 65 32 8546 123 646 564 6 54 If you put the following in G13: =SUMIF(D512, {"900","100"},G5:G12) it will return the same result as =SUMIF(D512, {"900"},G5:G12). The result of the first formula should be 65 less than the second. Alok wrote: Glenn, This does work but you have to enclose them in quotes. {"100","250"} Alok "C Glenn" wrote: Somewhat like an implicit OR but it's actually an implicity IN! I'm wondering if there are any other possibilities, i.e.: 250, 125. This doesn't work with the curlies. Thanks. Aladin Akyurek wrote: =SUM(SUMIF(B5:B12,{"D*","?D*"},D512)) C Glenn wrote: I'm trying to do this: =SUMIF(B5:B12,OR("=D*","=?D*"),D512) It returns zero each time. (BTW, =SUMIF(B5:B12,"=D*",D512) and =SUMIF(B5:B12,"=?D*",D512) both work just fine and return non zero sums.) So my question is, Where do I put the OR, or is there another way to do this? |
#8
|
|||
|
|||
OK, wait a minute. I did say OR originally; so, let me rephrase.
Instead of "The result of the first formula should be 65 less than the second", how about "wouldn't it be swell if we could implement this with a logical AND so that the result of the..." C Glenn wrote: I can't get this to work. In every case, the first criteria is applied and the second is ignored. Put these numbers in D, starting at D5: 45 123 789 4156 123 456 987 321 Now put these in G, starting at G5: 65 32 8546 123 646 564 6 54 If you put the following in G13: =SUMIF(D512, {"900","100"},G5:G12) it will return the same result as =SUMIF(D512, {"900"},G5:G12). The result of the first formula should be 65 less than the second. Alok wrote: Glenn, This does work but you have to enclose them in quotes. {"100","250"} Alok "C Glenn" wrote: Somewhat like an implicit OR but it's actually an implicity IN! I'm wondering if there are any other possibilities, i.e.: 250, 125. This doesn't work with the curlies. Thanks. Aladin Akyurek wrote: =SUM(SUMIF(B5:B12,{"D*","?D*"},D512)) C Glenn wrote: I'm trying to do this: =SUMIF(B5:B12,OR("=D*","=?D*"),D512) It returns zero each time. (BTW, =SUMIF(B5:B12,"=D*",D512) and =SUMIF(B5:B12,"=?D*",D512) both work just fine and return non zero sums.) So my question is, Where do I put the OR, or is there another way to do this? |
#9
|
|||
|
|||
Try...
=SUMPRODUCT(--(D512100),--(D512900),G5:G12) and =SUMIF(D512,"900",G5:G12) Hope this helps! In article , C Glenn wrote: I can't get this to work. In every case, the first criteria is applied and the second is ignored. Put these numbers in D, starting at D5: 45 123 789 4156 123 456 987 321 Now put these in G, starting at G5: 65 32 8546 123 646 564 6 54 If you put the following in G13: =SUMIF(D512, {"900","100"},G5:G12) it will return the same result as =SUMIF(D512, {"900"},G5:G12). The result of the first formula should be 65 less than the second. |
#10
|
|||
|
|||
Hi
This is happening because both functions are returning an array. The first function returns a single element in the array. The second function returns a two element array. If there is no function that operates on the array of values returned then Excel tends to use the first value of the array. If you enclose your Sumif within a Sum function like Aladin had done you will see that there is a difference. The second function will then return a number which is interpreted as the Sum of all numbers where D512 is less than 900 plus some of all numbers where the range D512 is greater than 100. This is different than the sum of all numbers where D512 is greater than 100 or less than 900 (which is basically all numbers.) In other words the method shown by Aladin has to be used cautiously - that is only in case of non-overlapping ranges. In other words this is probably OK =SUM(SUMIF(D512, {"100","900"},G5:G12)) if one intends to find the Sum of all numbers in G5 to G12 where the numbers in D512 are either smaller than 100 or greater than 900. Alok "C Glenn" wrote: I can't get this to work. In every case, the first criteria is applied and the second is ignored. Put these numbers in D, starting at D5: 45 123 789 4156 123 456 987 321 Now put these in G, starting at G5: 65 32 8546 123 646 564 6 54 If you put the following in G13: =SUMIF(D512, {"900","100"},G5:G12) it will return the same result as =SUMIF(D512, {"900"},G5:G12). The result of the first formula should be 65 less than the second. Alok wrote: Glenn, This does work but you have to enclose them in quotes. {"100","250"} Alok "C Glenn" wrote: Somewhat like an implicit OR but it's actually an implicity IN! I'm wondering if there are any other possibilities, i.e.: 250, 125. This doesn't work with the curlies. Thanks. Aladin Akyurek wrote: =SUM(SUMIF(B5:B12,{"D*","?D*"},D512)) C Glenn wrote: I'm trying to do this: =SUMIF(B5:B12,OR("=D*","=?D*"),D512) It returns zero each time. (BTW, =SUMIF(B5:B12,"=D*",D512) and =SUMIF(B5:B12,"=?D*",D512) both work just fine and return non zero sums.) So my question is, Where do I put the OR, or is there another way to do this? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to use SUMIF to return sums between two values located in cells | ScottBerger | Worksheet Functions | 1 | November 18th, 2004 07:09 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | Worksheet Functions | 7 | July 1st, 2004 10:22 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | General Discussion | 7 | July 1st, 2004 10:22 PM |
Multiple SUMIF functions ? | Frank Kabel | Worksheet Functions | 0 | February 26th, 2004 11:46 AM |
Narrow range in Sumif formula | Steve | Worksheet Functions | 2 | January 14th, 2004 06:16 PM |