| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum if and
I am new in here. Could someone give me a tip on how to manage this:
I have numbers in two columns (Aerea A2:B22). In A1 I want to sum up the values in A2:A22 where the cell in column A is =1000 OR the corresponding cell in column B is =1000 AND no cells must have the value null. In the small sample under the cells in A2 + A5 + A6 meets the criteria and the sum in A1 is then 2650. A B 1 2650 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 Someone have any clue ? SUMIF, SUMPRODUCT or nested if ? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and
Try this:
=SUMPRODUCT((ISNUMBER(A1:A6))*(ISNUMBER(B1:B6))*(A 1:B6=1000)*A1:A6) "tomjoe" wrote: I am new in here. Could someone give me a tip on how to manage this: I have numbers in two columns (Aerea A2:B22). In A1 I want to sum up the values in A2:A22 where the cell in column A is =1000 OR the corresponding cell in column B is =1000 AND no cells must have the value null. In the small sample under the cells in A2 + A5 + A6 meets the criteria and the sum in A1 is then 2650. A B 1 2650 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 Someone have any clue ? SUMIF, SUMPRODUCT or nested if ? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and
Try this:
column B is =1000 AND no cells must have the value null. What does "null" mean? Do you mean EMPTY or BLANK cells? =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6<""),A2:A6) Biff "tomjoe" wrote in message ... I am new in here. Could someone give me a tip on how to manage this: I have numbers in two columns (Aerea A2:B22). In A1 I want to sum up the values in A2:A22 where the cell in column A is =1000 OR the corresponding cell in column B is =1000 AND no cells must have the value null. In the small sample under the cells in A2 + A5 + A6 meets the criteria and the sum in A1 is then 2650. A B 1 2650 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 Someone have any clue ? SUMIF, SUMPRODUCT or nested if ? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and
the formulas posted will only work until you have values of 1000 or greater
in both columns A & B. Once this occurrs, the formulas will sum both columns A & B. From what you wrote, it didn't seem like that was what you wanted. Still working on fitting it into one formula... create another column, in this case C:C, but could be anywhere =IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all your data and then just sum the column. "Biff" wrote: Try this: column B is =1000 AND no cells must have the value null. What does "null" mean? Do you mean EMPTY or BLANK cells? =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6<""),A2:A6) Biff "tomjoe" wrote in message ... I am new in here. Could someone give me a tip on how to manage this: I have numbers in two columns (Aerea A2:B22). In A1 I want to sum up the values in A2:A22 where the cell in column A is =1000 OR the corresponding cell in column B is =1000 AND no cells must have the value null. In the small sample under the cells in A2 + A5 + A6 meets the criteria and the sum in A1 is then 2650. A B 1 2650 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 Someone have any clue ? SUMIF, SUMPRODUCT or nested if ? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and
the formulas posted will only work until you have values of 1000 or greater
in both columns A & B. Once this occurrs, the formulas will sum both columns A & B Good catch. Try this: =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)0),--(B2:B6<""),A2:A6) I'm not sure what they meant about the "null". EMPTY or BLANK cells in just column B or BOTH columns. Based on their description and expected result they did not mean null = number 0. Biff "watchtower" wrote in message ... the formulas posted will only work until you have values of 1000 or greater in both columns A & B. Once this occurrs, the formulas will sum both columns A & B. From what you wrote, it didn't seem like that was what you wanted. Still working on fitting it into one formula... create another column, in this case C:C, but could be anywhere =IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all your data and then just sum the column. "Biff" wrote: Try this: column B is =1000 AND no cells must have the value null. What does "null" mean? Do you mean EMPTY or BLANK cells? =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6<""),A2:A6) Biff "tomjoe" wrote in message ... I am new in here. Could someone give me a tip on how to manage this: I have numbers in two columns (Aerea A2:B22). In A1 I want to sum up the values in A2:A22 where the cell in column A is =1000 OR the corresponding cell in column B is =1000 AND no cells must have the value null. In the small sample under the cells in A2 + A5 + A6 meets the criteria and the sum in A1 is then 2650. A B 1 2650 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 Someone have any clue ? SUMIF, SUMPRODUCT or nested if ? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and
Sorry for the confusion. With "null" I mean the digit 0.
I see that I have a fault in my example, the right one is: A B 1 1250 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 The sum is 50 + 1200 = 1250 in A1. Only A2 and A5 meets the criteria of beeing =1000 in A OR B (not AND B) AND both cells must be different from 0. Also this is only a part of a big excel workbook and I have minor possibility to have the results in a column of it'sown, so I would prefer very much to have the the result in A1. Any suggestions ? tomjoe Biff skrev: the formulas posted will only work until you have values of 1000 or greater in both columns A & B. Once this occurrs, the formulas will sum both columns A & B Good catch. Try this: =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)0),--(B2:B6<""),A2:A6) I'm not sure what they meant about the "null". EMPTY or BLANK cells in just column B or BOTH columns. Based on their description and expected result they did not mean null = number 0. Biff "watchtower" wrote in message ... the formulas posted will only work until you have values of 1000 or greater in both columns A & B. Once this occurrs, the formulas will sum both columns A & B. From what you wrote, it didn't seem like that was what you wanted. Still working on fitting it into one formula... create another column, in this case C:C, but could be anywhere =IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all your data and then just sum the column. "Biff" wrote: Try this: column B is =1000 AND no cells must have the value null. What does "null" mean? Do you mean EMPTY or BLANK cells? =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6<""),A2:A6) Biff "tomjoe" wrote in message ... I am new in here. Could someone give me a tip on how to manage this: I have numbers in two columns (Aerea A2:B22). In A1 I want to sum up the values in A2:A22 where the cell in column A is =1000 OR the corresponding cell in column B is =1000 AND no cells must have the value null. In the small sample under the cells in A2 + A5 + A6 meets the criteria and the sum in A1 is then 2650. A B 1 2650 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 Someone have any clue ? SUMIF, SUMPRODUCT or nested if ? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and
Try this:
=SUMPRODUCT(--(A2:A6<0),--(B2:B6<0),--((A2:A6=1000)+(B2:B6=1000)0),A2:A6) Biff "tomjoe" wrote in message ... Sorry for the confusion. With "null" I mean the digit 0. I see that I have a fault in my example, the right one is: A B 1 1250 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 The sum is 50 + 1200 = 1250 in A1. Only A2 and A5 meets the criteria of beeing =1000 in A OR B (not AND B) AND both cells must be different from 0. Also this is only a part of a big excel workbook and I have minor possibility to have the results in a column of it'sown, so I would prefer very much to have the the result in A1. Any suggestions ? tomjoe Biff skrev: the formulas posted will only work until you have values of 1000 or greater in both columns A & B. Once this occurrs, the formulas will sum both columns A & B Good catch. Try this: =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)0),--(B2:B6<""),A2:A6) I'm not sure what they meant about the "null". EMPTY or BLANK cells in just column B or BOTH columns. Based on their description and expected result they did not mean null = number 0. Biff "watchtower" wrote in message ... the formulas posted will only work until you have values of 1000 or greater in both columns A & B. Once this occurrs, the formulas will sum both columns A & B. From what you wrote, it didn't seem like that was what you wanted. Still working on fitting it into one formula... create another column, in this case C:C, but could be anywhere =IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all your data and then just sum the column. "Biff" wrote: Try this: column B is =1000 AND no cells must have the value null. What does "null" mean? Do you mean EMPTY or BLANK cells? =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6<""),A2:A6) Biff "tomjoe" wrote in message ... I am new in here. Could someone give me a tip on how to manage this: I have numbers in two columns (Aerea A2:B22). In A1 I want to sum up the values in A2:A22 where the cell in column A is =1000 OR the corresponding cell in column B is =1000 AND no cells must have the value null. In the small sample under the cells in A2 + A5 + A6 meets the criteria and the sum in A1 is then 2650. A B 1 2650 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 Someone have any clue ? SUMIF, SUMPRODUCT or nested if ? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and
Thank you very much Biff. It worked fantastically.
You saved my day. I just had to put in semicolon instead of comma (don't now why ?) Just a question: I more or less could figure out the system in your code, and I also saw by testing that i had to have 0 before the next last brackets. But I couldn't figure out why. I would have thought that it was not necessary because we had allready said that A2:A6 and B2:B6 should not be 0. Biff skrev: Try this: =SUMPRODUCT(--(A2:A6<0),--(B2:B6<0),--((A2:A6=1000)+(B2:B6=1000)0),A2:A6) Biff "tomjoe" wrote in message ... Sorry for the confusion. With "null" I mean the digit 0. I see that I have a fault in my example, the right one is: A B 1 1250 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 The sum is 50 + 1200 = 1250 in A1. Only A2 and A5 meets the criteria of beeing =1000 in A OR B (not AND B) AND both cells must be different from 0. Also this is only a part of a big excel workbook and I have minor possibility to have the results in a column of it'sown, so I would prefer very much to have the the result in A1. Any suggestions ? tomjoe Biff skrev: the formulas posted will only work until you have values of 1000 or greater in both columns A & B. Once this occurrs, the formulas will sum both columns A & B Good catch. Try this: =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)0),--(B2:B6<""),A2:A6) I'm not sure what they meant about the "null". EMPTY or BLANK cells in just column B or BOTH columns. Based on their description and expected result they did not mean null = number 0. Biff "watchtower" wrote in message ... the formulas posted will only work until you have values of 1000 or greater in both columns A & B. Once this occurrs, the formulas will sum both columns A & B. From what you wrote, it didn't seem like that was what you wanted. Still working on fitting it into one formula... create another column, in this case C:C, but could be anywhere =IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all your data and then just sum the column. "Biff" wrote: Try this: column B is =1000 AND no cells must have the value null. What does "null" mean? Do you mean EMPTY or BLANK cells? =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6<""),A2:A6) Biff "tomjoe" wrote in message ... I am new in here. Could someone give me a tip on how to manage this: I have numbers in two columns (Aerea A2:B22). In A1 I want to sum up the values in A2:A22 where the cell in column A is =1000 OR the corresponding cell in column B is =1000 AND no cells must have the value null. In the small sample under the cells in A2 + A5 + A6 meets the criteria and the sum in A1 is then 2650. A B 1 2650 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 Someone have any clue ? SUMIF, SUMPRODUCT or nested if ? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and
If you are Scandinavian the default delimiter is semicolon whereas if you
are American you use comma -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "tomjoe" wrote in message ... Thank you very much Biff. It worked fantastically. You saved my day. I just had to put in semicolon instead of comma (don't now why ?) Just a question: I more or less could figure out the system in your code, and I also saw by testing that i had to have 0 before the next last brackets. But I couldn't figure out why. I would have thought that it was not necessary because we had allready said that A2:A6 and B2:B6 should not be 0. Biff skrev: Try this: =SUMPRODUCT(--(A2:A6<0),--(B2:B6<0),--((A2:A6=1000)+(B2:B6=1000)0),A2:A6) Biff "tomjoe" wrote in message ... Sorry for the confusion. With "null" I mean the digit 0. I see that I have a fault in my example, the right one is: A B 1 1250 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 The sum is 50 + 1200 = 1250 in A1. Only A2 and A5 meets the criteria of beeing =1000 in A OR B (not AND B) AND both cells must be different from 0. Also this is only a part of a big excel workbook and I have minor possibility to have the results in a column of it'sown, so I would prefer very much to have the the result in A1. Any suggestions ? tomjoe Biff skrev: the formulas posted will only work until you have values of 1000 or greater in both columns A & B. Once this occurrs, the formulas will sum both columns A & B Good catch. Try this: =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)0),--(B2:B6<""),A2:A6) I'm not sure what they meant about the "null". EMPTY or BLANK cells in just column B or BOTH columns. Based on their description and expected result they did not mean null = number 0. Biff "watchtower" wrote in message ... the formulas posted will only work until you have values of 1000 or greater in both columns A & B. Once this occurrs, the formulas will sum both columns A & B. From what you wrote, it didn't seem like that was what you wanted. Still working on fitting it into one formula... create another column, in this case C:C, but could be anywhere =IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all your data and then just sum the column. "Biff" wrote: Try this: column B is =1000 AND no cells must have the value null. What does "null" mean? Do you mean EMPTY or BLANK cells? =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6<""),A2:A6) Biff "tomjoe" wrote in message ... I am new in here. Could someone give me a tip on how to manage this: I have numbers in two columns (Aerea A2:B22). In A1 I want to sum up the values in A2:A22 where the cell in column A is =1000 OR the corresponding cell in column B is =1000 AND no cells must have the value null. In the small sample under the cells in A2 + A5 + A6 meets the criteria and the sum in A1 is then 2650. A B 1 2650 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 Someone have any clue ? SUMIF, SUMPRODUCT or nested if ? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and
--((A2:A6=1000)+(B2:B6=1000)0)
What that's doing is testing to see if there is at least one of two cells that has a value =1000. Let's use just the first row for an example: A2 = 50 B2 = 1500 =SUMPRODUCT(--(A2<0),--(B2<0),--((A2=1000)+(B2=1000)0),A2) A2<0 = TRUE .... --(A2<0) = 1 B2<0 = TRUE .... --(B2<0) = 1 A2=1000 = FALSE .... B2=1000 = TRUE (A2=1000)+(B2=1000) = (FALSE)+(TRUE) = (0+1) = ((0+1)0) = (10) = TRUE .... --(10) = 1 Array1 = --(A2<0) = 1 Array2 = --(B2<0) = 1 Array3 = --((A2=1000)+(B2=1000)0) = 1 Array4 = 50 (A2) So: =SUMPRODUCT(1,1,1,50) = 1 * 1 * 1 * 50 = 50 Hope that makes sense! Biff "tomjoe" wrote in message ... Thank you very much Biff. It worked fantastically. You saved my day. I just had to put in semicolon instead of comma (don't now why ?) Just a question: I more or less could figure out the system in your code, and I also saw by testing that i had to have 0 before the next last brackets. But I couldn't figure out why. I would have thought that it was not necessary because we had allready said that A2:A6 and B2:B6 should not be 0. Biff skrev: Try this: =SUMPRODUCT(--(A2:A6<0),--(B2:B6<0),--((A2:A6=1000)+(B2:B6=1000)0),A2:A6) Biff "tomjoe" wrote in message ... Sorry for the confusion. With "null" I mean the digit 0. I see that I have a fault in my example, the right one is: A B 1 1250 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 The sum is 50 + 1200 = 1250 in A1. Only A2 and A5 meets the criteria of beeing =1000 in A OR B (not AND B) AND both cells must be different from 0. Also this is only a part of a big excel workbook and I have minor possibility to have the results in a column of it'sown, so I would prefer very much to have the the result in A1. Any suggestions ? tomjoe Biff skrev: the formulas posted will only work until you have values of 1000 or greater in both columns A & B. Once this occurrs, the formulas will sum both columns A & B Good catch. Try this: =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)0),--(B2:B6<""),A2:A6) I'm not sure what they meant about the "null". EMPTY or BLANK cells in just column B or BOTH columns. Based on their description and expected result they did not mean null = number 0. Biff "watchtower" wrote in message ... the formulas posted will only work until you have values of 1000 or greater in both columns A & B. Once this occurrs, the formulas will sum both columns A & B. From what you wrote, it didn't seem like that was what you wanted. Still working on fitting it into one formula... create another column, in this case C:C, but could be anywhere =IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all your data and then just sum the column. "Biff" wrote: Try this: column B is =1000 AND no cells must have the value null. What does "null" mean? Do you mean EMPTY or BLANK cells? =SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6<""),A2:A6) Biff "tomjoe" wrote in message ... I am new in here. Could someone give me a tip on how to manage this: I have numbers in two columns (Aerea A2:B22). In A1 I want to sum up the values in A2:A22 where the cell in column A is =1000 OR the corresponding cell in column B is =1000 AND no cells must have the value null. In the small sample under the cells in A2 + A5 + A6 meets the criteria and the sum in A1 is then 2650. A B 1 2650 2 50 1500 3 0 1000 4 450 900 5 1200 850 6 1400 0 Someone have any clue ? SUMIF, SUMPRODUCT or nested if ? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
