Can I reference =, <, or > sign in SUMPRODUCT
Thanks to Biff for the last response.
One more for the group: Can I reference the "=", "<", or "" etc sign in a SUMPRODUCT function of the following form: "=SUMPRODUCT((Range1=Criteria1)*(Range2Criteria2) * (Range3<Criteria3)" I can use an indirect to reference the range, and direct references to the criteria. In countif and sumif functions I can direct reference "=", "<", or "" etc signs but can't seem to get it right for this sumproduct function. If you have a way, please check out the min, max and mode formulae below for the same question. Thanks BobT Hi! All are array formulas, enter with the key combo of CTRL,SHIFT,ENTER: =MODE(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1 :D10)))) =MIN(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1: D10)))) =MAX(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1: D10)))) Biff -----Original Message----- Thanks again for the previous response. Is there a way to get the mode min and or max with multiple criteria? -----Original Message----- You're close -- SUMPRODUCT does the job, but you've left off the final piece: "=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )* (Range3=Criteria3)*Range4)" "BobT" wrote in message ... I want to get an average based on multiple criteria without having to sort the records and identify the range. Of course I can get to the average if I can get the sum. I'm aware that I can sum records within a range that meet a codition outside the range using the SUMIF function "=SUMIF(Range,criteria,sum range)" I can also count records that meet multiple criteria using the SUMPRODUCT function "=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )* (Range3=Criteria3)" I've also seen this array formula to count records that meet multiple criteria: {=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,I F (Range3=Criteria3,1,0))) But the SUMPRODUCT formula is easier to work with. However, I have not found or figured out a sum formula for multple criteria. Any help out there? Thanks BobT |
That SUMPRODUCT formula already uses , < and =, so the answer is yes is it
not? -- HTH RP (remove nothere from the email address if mailing direct) "BobT" wrote in message ... Thanks to Biff for the last response. One more for the group: Can I reference the "=", "<", or "" etc sign in a SUMPRODUCT function of the following form: "=SUMPRODUCT((Range1=Criteria1)*(Range2Criteria2) * (Range3<Criteria3)" I can use an indirect to reference the range, and direct references to the criteria. In countif and sumif functions I can direct reference "=", "<", or "" etc signs but can't seem to get it right for this sumproduct function. If you have a way, please check out the min, max and mode formulae below for the same question. Thanks BobT Hi! All are array formulas, enter with the key combo of CTRL,SHIFT,ENTER: =MODE(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1 :D10)))) =MIN(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1: D10)))) =MAX(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1: D10)))) Biff -----Original Message----- Thanks again for the previous response. Is there a way to get the mode min and or max with multiple criteria? -----Original Message----- You're close -- SUMPRODUCT does the job, but you've left off the final piece: "=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )* (Range3=Criteria3)*Range4)" "BobT" wrote in message ... I want to get an average based on multiple criteria without having to sort the records and identify the range. Of course I can get to the average if I can get the sum. I'm aware that I can sum records within a range that meet a codition outside the range using the SUMIF function "=SUMIF(Range,criteria,sum range)" I can also count records that meet multiple criteria using the SUMPRODUCT function "=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )* (Range3=Criteria3)" I've also seen this array formula to count records that meet multiple criteria: {=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,I F (Range3=Criteria3,1,0))) But the SUMPRODUCT formula is easier to work with. However, I have not found or figured out a sum formula for multple criteria. Any help out there? Thanks BobT |
I'm trying to refence the range and criteria so that I can
change them easily to look at different columns of data and different criteria. Consider the following where ranges are defined in column A and criteria in Column B. =SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)*(I NDIRECT (A3)=B3)*(INDIRECT(A4)=B4)) This works, but if I try to reference the = sign or a < or sign in column C that doesn't work. It will work in a sumif or countif function =Sumif(indirect(A1),C1&B1) where ranges are defined in column A, criteria in Column B, and =, <, , =<, =, or < in column C. -----Original Message----- That SUMPRODUCT formula already uses , < and =, so the answer is yes is it not? -- HTH RP (remove nothere from the email address if mailing direct) "BobT" wrote in message ... Thanks to Biff for the last response. One more for the group: Can I reference the "=", "<", or "" etc sign in a SUMPRODUCT function of the following form: "=SUMPRODUCT((Range1=Criteria1)*(Range2Criteria2) * (Range3<Criteria3)" I can use an indirect to reference the range, and direct references to the criteria. In countif and sumif functions I can direct reference "=", "<", or "" etc signs but can't seem to get it right for this sumproduct function. If you have a way, please check out the min, max and mode formulae below for the same question. Thanks BobT Hi! All are array formulas, enter with the key combo of CTRL,SHIFT,ENTER: =MODE(IF(A1:A10="x",IF(B1:B10="y",IF (C1:C10="z",D1:D10)))) =MIN(IF(A1:A10="x",IF(B1:B10="y",IF (C1:C10="z",D1:D10)))) =MAX(IF(A1:A10="x",IF(B1:B10="y",IF (C1:C10="z",D1:D10)))) Biff -----Original Message----- Thanks again for the previous response. Is there a way to get the mode min and or max with multiple criteria? -----Original Message----- You're close -- SUMPRODUCT does the job, but you've left off the final piece: "=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )* (Range3=Criteria3)*Range4)" "BobT" wrote in message ... I want to get an average based on multiple criteria without having to sort the records and identify the range. Of course I can get to the average if I can get the sum. I'm aware that I can sum records within a range that meet a codition outside the range using the SUMIF function "=SUMIF(Range,criteria,sum range)" I can also count records that meet multiple criteria using the SUMPRODUCT function "=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )* (Range3=Criteria3)" I've also seen this array formula to count records that meet multiple criteria: {=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,I F (Range3=Criteria3,1,0))) But the SUMPRODUCT formula is easier to work with. However, I have not found or figured out a sum formula for multple criteria. Any help out there? Thanks BobT . |
This works fine for me
=SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)*( INDIRECT(A3)=B3)*(INDIRECT (A4)=B4)) -- HTH RP (remove nothere from the email address if mailing direct) "BobT" wrote in message ... I'm trying to refence the range and criteria so that I can change them easily to look at different columns of data and different criteria. Consider the following where ranges are defined in column A and criteria in Column B. =SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)*(I NDIRECT (A3)=B3)*(INDIRECT(A4)=B4)) This works, but if I try to reference the = sign or a < or sign in column C that doesn't work. It will work in a sumif or countif function =Sumif(indirect(A1),C1&B1) where ranges are defined in column A, criteria in Column B, and =, <, , =<, =, or < in column C. -----Original Message----- That SUMPRODUCT formula already uses , < and =, so the answer is yes is it not? -- HTH RP (remove nothere from the email address if mailing direct) "BobT" wrote in message ... Thanks to Biff for the last response. One more for the group: Can I reference the "=", "<", or "" etc sign in a SUMPRODUCT function of the following form: "=SUMPRODUCT((Range1=Criteria1)*(Range2Criteria2) * (Range3<Criteria3)" I can use an indirect to reference the range, and direct references to the criteria. In countif and sumif functions I can direct reference "=", "<", or "" etc signs but can't seem to get it right for this sumproduct function. If you have a way, please check out the min, max and mode formulae below for the same question. Thanks BobT Hi! All are array formulas, enter with the key combo of CTRL,SHIFT,ENTER: =MODE(IF(A1:A10="x",IF(B1:B10="y",IF (C1:C10="z",D1:D10)))) =MIN(IF(A1:A10="x",IF(B1:B10="y",IF (C1:C10="z",D1:D10)))) =MAX(IF(A1:A10="x",IF(B1:B10="y",IF (C1:C10="z",D1:D10)))) Biff -----Original Message----- Thanks again for the previous response. Is there a way to get the mode min and or max with multiple criteria? -----Original Message----- You're close -- SUMPRODUCT does the job, but you've left off the final piece: "=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )* (Range3=Criteria3)*Range4)" "BobT" wrote in message ... I want to get an average based on multiple criteria without having to sort the records and identify the range. Of course I can get to the average if I can get the sum. I'm aware that I can sum records within a range that meet a codition outside the range using the SUMIF function "=SUMIF(Range,criteria,sum range)" I can also count records that meet multiple criteria using the SUMPRODUCT function "=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )* (Range3=Criteria3)" I've also seen this array formula to count records that meet multiple criteria: {=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,I F (Range3=Criteria3,1,0))) But the SUMPRODUCT formula is easier to work with. However, I have not found or figured out a sum formula for multple criteria. Any help out there? Thanks BobT . |
Right
I can reference the range and the criteria, but not the =, <, etc condition. I want to be able to reference the condition also. I'm trying to set up a cross tab for survey data that is flexible so it can adapted to different surveys. I might want to show an age range in one column, requiring = and <= conditions, but in another column i might want females in the Northeast, requiring two = conditions. I want to be able to change the =, <, condtions at the top of the column and have the SUMPRODUCT function reference that without going into the formula and changing =, <, conditions to suit my specific needs. -----Original Message----- This works fine for me =SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)* (INDIRECT (A3)=B3)*(INDIRECT (A4)=B4)) -- HTH RP (remove nothere from the email address if mailing direct) "BobT" wrote in message ... I'm trying to refence the range and criteria so that I can change them easily to look at different columns of data and different criteria. Consider the following where ranges are defined in column A and criteria in Column B. =SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)* (INDIRECT (A3)=B3)*(INDIRECT(A4)=B4)) This works, but if I try to reference the = sign or a < or sign in column C that doesn't work. It will work in a sumif or countif function =Sumif(indirect(A1),C1&B1) where ranges are defined in column A, criteria in Column B, and =, <, , =<, =, or < in column C. -----Original Message----- That SUMPRODUCT formula already uses , < and =, so the answer is yes is it not? -- HTH RP (remove nothere from the email address if mailing direct) "BobT" wrote in message ... Thanks to Biff for the last response. One more for the group: Can I reference the "=", "<", or "" etc sign in a SUMPRODUCT function of the following form: "=SUMPRODUCT((Range1=Criteria1)*(Range2Criteria2) * (Range3<Criteria3)" I can use an indirect to reference the range, and direct references to the criteria. In countif and sumif functions I can direct reference "=", "<", or "" etc signs but can't seem to get it right for this sumproduct function. If you have a way, please check out the min, max and mode formulae below for the same question. Thanks BobT Hi! All are array formulas, enter with the key combo of CTRL,SHIFT,ENTER: =MODE(IF(A1:A10="x",IF(B1:B10="y",IF (C1:C10="z",D1:D10)))) =MIN(IF(A1:A10="x",IF(B1:B10="y",IF (C1:C10="z",D1:D10)))) =MAX(IF(A1:A10="x",IF(B1:B10="y",IF (C1:C10="z",D1:D10)))) Biff -----Original Message----- Thanks again for the previous response. Is there a way to get the mode min and or max with multiple criteria? -----Original Message----- You're close -- SUMPRODUCT does the job, but you've left off the final piece: "=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )* (Range3=Criteria3)*Range4)" "BobT" wrote in message ... I want to get an average based on multiple criteria without having to sort the records and identify the range. Of course I can get to the average if I can get the sum. I'm aware that I can sum records within a range that meet a codition outside the range using the SUMIF function "=SUMIF(Range,criteria,sum range)" I can also count records that meet multiple criteria using the SUMPRODUCT function "=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )* (Range3=Criteria3)" I've also seen this array formula to count records that meet multiple criteria: {=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,I F (Range3=Criteria3,1,0))) But the SUMPRODUCT formula is easier to work with. However, I have not found or figured out a sum formula for multple criteria. Any help out there? Thanks BobT . . |
You'd need to use a macro for that, maybe an input box. I personally avoid
using indirect because it is volatile and IMHO it is overkill -- Regards, Peo Sjoblom "BobT" wrote in message ... Right I can reference the range and the criteria, but not the =, <, etc condition. I want to be able to reference the condition also. I'm trying to set up a cross tab for survey data that is flexible so it can adapted to different surveys. I might want to show an age range in one column, requiring = and <= conditions, but in another column i might want females in the Northeast, requiring two = conditions. I want to be able to change the =, <, condtions at the top of the column and have the SUMPRODUCT function reference that without going into the formula and changing =, <, conditions to suit my specific needs. -----Original Message----- This works fine for me =SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)* (INDIRECT (A3)=B3)*(INDIRECT (A4)=B4)) -- HTH RP (remove nothere from the email address if mailing direct) "BobT" wrote in message ... I'm trying to refence the range and criteria so that I can change them easily to look at different columns of data and different criteria. Consider the following where ranges are defined in column A and criteria in Column B. =SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)* (INDIRECT (A3)=B3)*(INDIRECT(A4)=B4)) This works, but if I try to reference the = sign or a < or sign in column C that doesn't work. It will work in a sumif or countif function =Sumif(indirect(A1),C1&B1) where ranges are defined in column A, criteria in Column B, and =, <, , =<, =, or < in column C. -----Original Message----- That SUMPRODUCT formula already uses , < and =, so the answer is yes is it not? -- HTH RP (remove nothere from the email address if mailing direct) "BobT" wrote in message ... Thanks to Biff for the last response. One more for the group: Can I reference the "=", "<", or "" etc sign in a SUMPRODUCT function of the following form: "=SUMPRODUCT((Range1=Criteria1)*(Range2Criteria2) * (Range3<Criteria3)" I can use an indirect to reference the range, and direct references to the criteria. In countif and sumif functions I can direct reference "=", "<", or "" etc signs but can't seem to get it right for this sumproduct function. If you have a way, please check out the min, max and mode formulae below for the same question. Thanks BobT Hi! All are array formulas, enter with the key combo of CTRL,SHIFT,ENTER: =MODE(IF(A1:A10="x",IF(B1:B10="y",IF (C1:C10="z",D1:D10)))) =MIN(IF(A1:A10="x",IF(B1:B10="y",IF (C1:C10="z",D1:D10)))) =MAX(IF(A1:A10="x",IF(B1:B10="y",IF (C1:C10="z",D1:D10)))) Biff -----Original Message----- Thanks again for the previous response. Is there a way to get the mode min and or max with multiple criteria? -----Original Message----- You're close -- SUMPRODUCT does the job, but you've left off the final piece: "=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )* (Range3=Criteria3)*Range4)" "BobT" wrote in message ... I want to get an average based on multiple criteria without having to sort the records and identify the range. Of course I can get to the average if I can get the sum. I'm aware that I can sum records within a range that meet a codition outside the range using the SUMIF function "=SUMIF(Range,criteria,sum range)" I can also count records that meet multiple criteria using the SUMPRODUCT function "=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )* (Range3=Criteria3)" I've also seen this array formula to count records that meet multiple criteria: {=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,I F (Range3=Criteria3,1,0))) But the SUMPRODUCT formula is easier to work with. However, I have not found or figured out a sum formula for multple criteria. Any help out there? Thanks BobT . . |
How about adding a unique pattern of spaces to the operator in the
formula and then doing a Replace? Example: (^ are spaces) =Sumproduct((Range1^=^Criteria1)*(Range2^^^Criter ia2)*(Range3^^^<^Criteria3)) This prevents the same operators in different locations from being changed. |
Thanks
I was resigned to writing a macro or manually using replace to change the operator when needed. This tip will be very helpful either way. -----Original Message----- How about adding a unique pattern of spaces to the operator in the formula and then doing a Replace? Example: (^ are spaces) =Sumproduct((Range1^=^Criteria1)*(Range2^^^Crite ria2)* (Range3^^^<^Criteria3)) This prevents the same operators in different locations from being changed. . |
All times are GMT +1. The time now is 09:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com