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 |
#21
|
|||
|
|||
SUMIF with 2 conditions
Ooops! I used the wrong sum range in the long formula:
...+(H7:H71=1)0),J7:J71) Should be: ....+(H7:H71=1)0),AE7:AE71) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Is that then an OR operation like this If I understand what you want to do, it is, but you need to do it like this: =SUMPRODUCT(--((A7:A71=1)+(B7:B71=1)+(C7:C71=1)+(D771=1)+( E7:E71=1)+(F7:F71=1)+(G7:G71=1)+(H7:H71=1)0), J7:J71) See if this does what you want: =SUMPRODUCT(--(MMULT(--(A7:H71=1),{1;1;1;1;1;1;1;1})0),AE7:AE71) -- Biff Microsoft Excel MVP "JEB" wrote in message ... Well, yes, I'm using the SUMPRODUCT because I have to look at more columns, but this was just testing it for one column first. Now I have: =SUMPRODUCT(($A$7:$A$71=1)*($B$7:$B$71=1)*($C$7: $C$71=1)*($D$7:$D$71=1)*($E$7:$E$71=1)*($F$7:$F $71=1)*($G$7:$G$71=1)*($H$7:$H$71=1)*($AE$7:$AE $71)) And while it worked for just A7:A71, it doesn't seem to be working for cols B thru H. I"m sure the root cause is because it is returning a zero for at least one of the columns and therefore not adding that row value. So, is there a better way to do this? I'm scanning an array of cells (A7:H71) and if any of them have a number =1 in the cell, then it is to add the corresponding value in column AE. Is that then an OR operation like this: =SUMPRODUCT(($A$7:$A$71=1)+($B$7:$B$71=1)+($C$7: $C$71=1)+..*($AE$7:$AE$71)) That seems to count, then multiply by the final row. Hmmm, I've led myself astray, I think. "T. Valko" wrote: =SUMPRODUCT((A7:A72="=1")*AD7:AD72) OK, here's what that formula is doing... It's looking in the range A7:A72 for the *TEXT string* =1. I'll bet you want that to be: =SUMPRODUCT((A7:A72=1)*AD7:AD72) Which can also be done with a simple SUMIF: =SUMIF(A7:A72,"=1",AD7:AD72) With SUMIF and COUNTIF (and SUMIFS and COUNTIFS in Excel 2007) you quote the criteria operator but in other functions you don't. -- Biff Microsoft Excel MVP |
#22
|
|||
|
|||
SUMIF with 2 conditions
YES! Wow, Biff! That's awesome!
Thank you soooo much! "T. Valko" wrote: Ooops! I used the wrong sum range in the long formula: ...+(H7:H71=1)0),J7:J71) Should be: ....+(H7:H71=1)0),AE7:AE71) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Is that then an OR operation like this If I understand what you want to do, it is, but you need to do it like this: =SUMPRODUCT(--((A7:A71=1)+(B7:B71=1)+(C7:C71=1)+(D771=1)+( E7:E71=1)+(F7:F71=1)+(G7:G71=1)+(H7:H71=1)0), J7:J71) See if this does what you want: =SUMPRODUCT(--(MMULT(--(A7:H71=1),{1;1;1;1;1;1;1;1})0),AE7:AE71) -- Biff Microsoft Excel MVP "JEB" wrote in message ... Well, yes, I'm using the SUMPRODUCT because I have to look at more columns, but this was just testing it for one column first. Now I have: =SUMPRODUCT(($A$7:$A$71=1)*($B$7:$B$71=1)*($C$7: $C$71=1)*($D$7:$D$71=1)*($E$7:$E$71=1)*($F$7:$F $71=1)*($G$7:$G$71=1)*($H$7:$H$71=1)*($AE$7:$AE $71)) And while it worked for just A7:A71, it doesn't seem to be working for cols B thru H. I"m sure the root cause is because it is returning a zero for at least one of the columns and therefore not adding that row value. So, is there a better way to do this? I'm scanning an array of cells (A7:H71) and if any of them have a number =1 in the cell, then it is to add the corresponding value in column AE. Is that then an OR operation like this: =SUMPRODUCT(($A$7:$A$71=1)+($B$7:$B$71=1)+($C$7: $C$71=1)+..*($AE$7:$AE$71)) That seems to count, then multiply by the final row. Hmmm, I've led myself astray, I think. "T. Valko" wrote: =SUMPRODUCT((A7:A72="=1")*AD7:AD72) OK, here's what that formula is doing... It's looking in the range A7:A72 for the *TEXT string* =1. I'll bet you want that to be: =SUMPRODUCT((A7:A72=1)*AD7:AD72) Which can also be done with a simple SUMIF: =SUMIF(A7:A72,"=1",AD7:AD72) With SUMIF and COUNTIF (and SUMIFS and COUNTIFS in Excel 2007) you quote the criteria operator but in other functions you don't. -- Biff Microsoft Excel MVP . |
#23
|
|||
|
|||
SUMIF with 2 conditions
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "JEB" wrote in message ... YES! Wow, Biff! That's awesome! Thank you soooo much! "T. Valko" wrote: Ooops! I used the wrong sum range in the long formula: ...+(H7:H71=1)0),J7:J71) Should be: ....+(H7:H71=1)0),AE7:AE71) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Is that then an OR operation like this If I understand what you want to do, it is, but you need to do it like this: =SUMPRODUCT(--((A7:A71=1)+(B7:B71=1)+(C7:C71=1)+(D771=1)+( E7:E71=1)+(F7:F71=1)+(G7:G71=1)+(H7:H71=1)0), J7:J71) See if this does what you want: =SUMPRODUCT(--(MMULT(--(A7:H71=1),{1;1;1;1;1;1;1;1})0),AE7:AE71) -- Biff Microsoft Excel MVP "JEB" wrote in message ... Well, yes, I'm using the SUMPRODUCT because I have to look at more columns, but this was just testing it for one column first. Now I have: =SUMPRODUCT(($A$7:$A$71=1)*($B$7:$B$71=1)*($C$7: $C$71=1)*($D$7:$D$71=1)*($E$7:$E$71=1)*($F$7:$F $71=1)*($G$7:$G$71=1)*($H$7:$H$71=1)*($AE$7:$AE $71)) And while it worked for just A7:A71, it doesn't seem to be working for cols B thru H. I"m sure the root cause is because it is returning a zero for at least one of the columns and therefore not adding that row value. So, is there a better way to do this? I'm scanning an array of cells (A7:H71) and if any of them have a number =1 in the cell, then it is to add the corresponding value in column AE. Is that then an OR operation like this: =SUMPRODUCT(($A$7:$A$71=1)+($B$7:$B$71=1)+($C$7: $C$71=1)+..*($AE$7:$AE$71)) That seems to count, then multiply by the final row. Hmmm, I've led myself astray, I think. "T. Valko" wrote: =SUMPRODUCT((A7:A72="=1")*AD7:AD72) OK, here's what that formula is doing... It's looking in the range A7:A72 for the *TEXT string* =1. I'll bet you want that to be: =SUMPRODUCT((A7:A72=1)*AD7:AD72) Which can also be done with a simple SUMIF: =SUMIF(A7:A72,"=1",AD7:AD72) With SUMIF and COUNTIF (and SUMIFS and COUNTIFS in Excel 2007) you quote the criteria operator but in other functions you don't. -- Biff Microsoft Excel MVP . |
Thread Tools | |
Display Modes | |
|
|