| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONDITIONAL SUM ("OR")
Sure would oblige if any of you experts devise a formula to calculate the sum
of column E of the following table, wherever an "X" is present in any of the corresponding cells of Column A, B, C or D (i.e. resulting to 268326): X Z Y Y 48421 Y W Z Z 60492 X X X X 22073 W X W W 19299 Z Z Y X 34706 Y W Y W 83035 V Z Z V 64431 X W X W 76684 Y Y Z Z 15703 V Z W W 11638 Z X W V 15573 V X Z X 11751 X Y Y X 39819 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CONDITIONAL SUM ("OR")
One way:
=SUMPRODUCT(--(MMULT(--(A1:D13="x"),{1;1;1;1})0),E1:E13) Note that the MMULT function is limited to a range of 5461 rows. -- Biff Microsoft Excel MVP "FARAZ QURESHI" wrote in message ... Sure would oblige if any of you experts devise a formula to calculate the sum of column E of the following table, wherever an "X" is present in any of the corresponding cells of Column A, B, C or D (i.e. resulting to 268326): X Z Y Y 48421 Y W Z Z 60492 X X X X 22073 W X W W 19299 Z Z Y X 34706 Y W Y W 83035 V Z Z V 64431 X W X W 76684 Y Y Z Z 15703 V Z W W 11638 Z X W V 15573 V X Z X 11751 X Y Y X 39819 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CONDITIONAL SUM ("OR")
Try this...
=SUMPRODUCT(--((A1:A25="X")+(B1:B25="X")+(C1:C25="X")+(D1:D25="X ")0),--E1:E25) If this post helps click Yes --------------- Jacob Skaria "FARAZ QURESHI" wrote: Sure would oblige if any of you experts devise a formula to calculate the sum of column E of the following table, wherever an "X" is present in any of the corresponding cells of Column A, B, C or D (i.e. resulting to 268326): X Z Y Y 48421 Y W Z Z 60492 X X X X 22073 W X W W 19299 Z Z Y X 34706 Y W Y W 83035 V Z Z V 64431 X W X W 76684 Y Y Z Z 15703 V Z W W 11638 Z X W V 15573 V X Z X 11751 X Y Y X 39819 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CONDITIONAL SUM ("OR")
Man that's great Jacob!
Never had the idea of using "" for neglecting double counting! XClent. "Jacob Skaria" wrote: Try this... =SUMPRODUCT(--((A1:A25="X")+(B1:B25="X")+(C1:C25="X")+(D1:D25="X ")0),--E1:E25) If this post helps click Yes --------------- Jacob Skaria "FARAZ QURESHI" wrote: Sure would oblige if any of you experts devise a formula to calculate the sum of column E of the following table, wherever an "X" is present in any of the corresponding cells of Column A, B, C or D (i.e. resulting to 268326): X Z Y Y 48421 Y W Z Z 60492 X X X X 22073 W X W W 19299 Z Z Y X 34706 Y W Y W 83035 V Z Z V 64431 X W X W 76684 Y Y Z Z 15703 V Z W W 11638 Z X W V 15573 V X Z X 11751 X Y Y X 39819 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CONDITIONAL SUM ("OR")
one way:
=SUM(IF(($A$1:$A$13="X")+($B$1:$B$13="X")+($C$1:$C $13="X")+($D$1:$D $13="X")+($E$1:$E$13="X"),$F$1:$F$13)) this is an array-formula so CTRL+SHIFT+ENTER it instead of simply entering adjust ranges to suit HIH On 13 Maj, 07:44, FARAZ QURESHI wrote: Sure would oblige if any of you experts devise a formula to calculate the sum of column E of the following table, wherever an "X" is present in any of the corresponding cells of Column A, B, C or D (i.e. resulting to 268326): *X * * * Z * * * Y * * * Y * * *48421 *Y * * * W * * * Z * * * Z * * *60492 *X * * * X * * * X * * * X * * *22073 *W * * * X * * * W * * * W * * *19299 *Z * * * Z * * * Y * * * X * * *34706 *Y * * * W * * * Y * * * W * * *83035 *V * * * Z * * * Z * * * V * * *64431 *X * * * W * * * X * * * W * * *76684 *Y * * * Y * * * Z * * * Z * * *15703 *V * * * Z * * * W * * * W * * *11638 *Z * * * X * * * W * * * V * * *15573 *V * * * X * * * Z * * * X * * *11751 *X * * * Y * * * Y * * * X * * *39819 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CONDITIONAL SUM ("OR")
corrected
=SUM(IF(($A$1:$A$13="X")+($B$1:$B$13="X")+($C$1:$C $13="X")+($D$1:$D $13="X"),$F$1:$F$13)) sorry On 13 Maj, 09:32, Jarek Kujawa wrote: one way: =SUM(IF(($A$1:$A$13="X")+($B$1:$B$13="X")+($C$1:$C $13="X")+($D$1:$D $13="X")+($E$1:$E$13="X"),$F$1:$F$13)) this is an array-formula so CTRL+SHIFT+ENTER it instead of simply entering adjust ranges to suit HIH On 13 Maj, 07:44, FARAZ QURESHI wrote: Sure would oblige if any of you experts devise a formula to calculate the sum of column E of the following table, wherever an "X" is present in any of the corresponding cells of Column A, B, C or D (i.e. resulting to 268326): *X * * * Z * * * Y * * * Y * * *48421 *Y * * * W * * * Z * * * Z * * *60492 *X * * * X * * * X * * * X * * *22073 *W * * * X * * * W * * * W * * *19299 *Z * * * Z * * * Y * * * X * * *34706 *Y * * * W * * * Y * * * W * * *83035 *V * * * Z * * * Z * * * V * * *64431 *X * * * W * * * X * * * W * * *76684 *Y * * * Y * * * Z * * * Z * * *15703 *V * * * Z * * * W * * * W * * *11638 *Z * * * X * * * W * * * V * * *15573 *V * * * X * * * Z * * * X * * *11751 *X * * * Y * * * Y * * * X * * *39819- Ukryj cytowany tekst - - Poka¿ cytowany tekst - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CONDITIONAL SUM ("OR")
FARAZ ,thanks for your feedback..
-- If this post helps click Yes --------------- Jacob Skaria "FARAZ QURESHI" wrote: Man that's great Jacob! Never had the idea of using "" for neglecting double counting! XClent. "Jacob Skaria" wrote: Try this... =SUMPRODUCT(--((A1:A25="X")+(B1:B25="X")+(C1:C25="X")+(D1:D25="X ")0),--E1:E25) If this post helps click Yes --------------- Jacob Skaria "FARAZ QURESHI" wrote: Sure would oblige if any of you experts devise a formula to calculate the sum of column E of the following table, wherever an "X" is present in any of the corresponding cells of Column A, B, C or D (i.e. resulting to 268326): X Z Y Y 48421 Y W Z Z 60492 X X X X 22073 W X W W 19299 Z Z Y X 34706 Y W Y W 83035 V Z Z V 64431 X W X W 76684 Y Y Z Z 15703 V Z W W 11638 Z X W V 15573 V X Z X 11751 X Y Y X 39819 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CONDITIONAL SUM ("OR")
Further corrected
=SUM(IF(($A$1:$A$13="X")+($B$1:$B$13="X")+($C$1:$C $13="X")+($D$1:$D$13="X"),$E$1:$E$13)) "Jarek Kujawa" wrote: corrected =SUM(IF(($A$1:$A$13="X")+($B$1:$B$13="X")+($C$1:$C $13="X")+($D$1:$D $13="X"),$F$1:$F$13)) sorry On 13 Maj, 09:32, Jarek Kujawa wrote: one way: =SUM(IF(($A$1:$A$13="X")+($B$1:$B$13="X")+($C$1:$C $13="X")+($D$1:$D $13="X")+($E$1:$E$13="X"),$F$1:$F$13)) this is an array-formula so CTRL+SHIFT+ENTER it instead of simply entering adjust ranges to suit HIH On 13 Maj, 07:44, FARAZ QURESHI wrote: Sure would oblige if any of you experts devise a formula to calculate the sum of column E of the following table, wherever an "X" is present in any of the corresponding cells of Column A, B, C or D (i.e. resulting to 268326): X Z Y Y 48421 Y W Z Z 60492 X X X X 22073 W X W W 19299 Z Z Y X 34706 Y W Y W 83035 V Z Z V 64431 X W X W 76684 Y Y Z Z 15703 V Z W W 11638 Z X W V 15573 V X Z X 11751 X Y Y X 39819- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CONDITIONAL SUM ("OR")
yet another way:
=SUM(IF(COUNTIF(OFFSET($A$1,ROW(1:13)-1,,1,4),"X"),F1:F13)) array-entered On 13 Maj, 09:35, Jarek Kujawa wrote: corrected =SUM(IF(($A$1:$A$13="X")+($B$1:$B$13="X")+($C$1:$C $13="X")+($D$1:$D $13="X"),$F$1:$F$13)) sorry On 13 Maj, 09:32, Jarek Kujawa wrote: one way: =SUM(IF(($A$1:$A$13="X")+($B$1:$B$13="X")+($C$1:$C $13="X")+($D$1:$D $13="X")+($E$1:$E$13="X"),$F$1:$F$13)) this is an array-formula so CTRL+SHIFT+ENTER it instead of simply entering adjust ranges to suit HIH On 13 Maj, 07:44, FARAZ QURESHI wrote: Sure would oblige if any of you experts devise a formula to calculate the sum of column E of the following table, wherever an "X" is present in any of the corresponding cells of Column A, B, C or D (i.e. resulting to 268326): Â*X Â* Â* Â* Z Â* Â* Â* Y Â* Â* Â* Y Â* Â* Â*48421 Â*Y Â* Â* Â* W Â* Â* Â* Z Â* Â* Â* Z Â* Â* Â*60492 Â*X Â* Â* Â* X Â* Â* Â* X Â* Â* Â* X Â* Â* Â*22073 Â*W Â* Â* Â* X Â* Â* Â* W Â* Â* Â* W Â* Â* Â*19299 Â*Z Â* Â* Â* Z Â* Â* Â* Y Â* Â* Â* X Â* Â* Â*34706 Â*Y Â* Â* Â* W Â* Â* Â* Y Â* Â* Â* W Â* Â* Â*83035 Â*V Â* Â* Â* Z Â* Â* Â* Z Â* Â* Â* V Â* Â* Â*64431 Â*X Â* Â* Â* W Â* Â* Â* X Â* Â* Â* W Â* Â* Â*76684 Â*Y Â* Â* Â* Y Â* Â* Â* Z Â* Â* Â* Z Â* Â* Â*15703 Â*V Â* Â* Â* Z Â* Â* Â* W Â* Â* Â* W Â* Â* Â*11638 Â*Z Â* Â* Â* X Â* Â* Â* W Â* Â* Â* V Â* Â* Â*15573 Â*V Â* Â* Â* X Â* Â* Â* Z Â* Â* Â* X Â* Â* Â*11751 Â*X Â* Â* Â* Y Â* Â* Â* Y Â* Â* Â* X Â* Â* Â*39819- Ukryj cytowany tekst - - Poka¿ cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003: Conditional Formatting using "MIN" & "MAX" function | Excel Discussion (Misc queries) | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| inserting a conditional "go to" command on a excel "if" function | Excel Worksheet Functions | |||
| conditional formula to show "open" or "closed" | Excel Worksheet Functions | |||
Linear Mode
