| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
complicated multi column formula help
This is what my data is:
B= BLIND SCORE TOTALS GM1 GM2 GM3 GM4 GM1 GM2 GM3 GM4 SERIES BOWLED # OF GAMES D E F G H I J K M O B B 0 0 100 100 150 150 300 2 D THRU G WILL EITHER = "B" OR "0" H THRU K WILL EITHER = 0 OR 0 I NEED "M" TO ONLY TOTAL VALUE OF "H" THRU "K" ONLY WHEN "D" THRU "G" = 0 I NEED "O" TO ONLY BE +1 EACH TIME "H" THRU "K" 0 & "D" THRU "G" = 0 I know what I want but, not even sure if I am explaining it correctly. PLEASE HELP ME. Thanks, Pat |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
complicated multi column formula help
Hi Pat,
Try these formulas: In M1: =SUM(IF(D1=0, H1,0)+IF(E1=0, I1,0)+IF(F1=0, J1,0)+IF(G1=0, K1,0)) In O1: =SUM(IF(AND(D1=0, H10),1,0)+IF(AND(E1=0, I10),1,0)+IF(AND(F1=0, J10),1,0)+IF(AND(G1=0, K10),1,0)) -- Ken Hudson "Pat" wrote: This is what my data is: B= BLIND SCORE TOTALS GM1 GM2 GM3 GM4 GM1 GM2 GM3 GM4 SERIES BOWLED # OF GAMES D E F G H I J K M O B B 0 0 100 100 150 150 300 2 D THRU G WILL EITHER = "B" OR "0" H THRU K WILL EITHER = 0 OR 0 I NEED "M" TO ONLY TOTAL VALUE OF "H" THRU "K" ONLY WHEN "D" THRU "G" = 0 I NEED "O" TO ONLY BE +1 EACH TIME "H" THRU "K" 0 & "D" THRU "G" = 0 I know what I want but, not even sure if I am explaining it correctly. PLEASE HELP ME. Thanks, Pat |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
complicated multi column formula help
For Series Bowled:
=SUMIF(D2:G2,0,H2:K2) For # of Games: =SUMPRODUCT(--(ISNUMBER(D2:G2)),--(D2:G2=0),--(H2:K20)) Biff "Pat" wrote in message ... This is what my data is: B= BLIND SCORE TOTALS GM1 GM2 GM3 GM4 GM1 GM2 GM3 GM4 SERIES BOWLED # OF GAMES D E F G H I J K M O B B 0 0 100 100 150 150 300 2 D THRU G WILL EITHER = "B" OR "0" H THRU K WILL EITHER = 0 OR 0 I NEED "M" TO ONLY TOTAL VALUE OF "H" THRU "K" ONLY WHEN "D" THRU "G" = 0 I NEED "O" TO ONLY BE +1 EACH TIME "H" THRU "K" 0 & "D" THRU "G" = 0 I know what I want but, not even sure if I am explaining it correctly. PLEASE HELP ME. Thanks, Pat |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
complicated multi column formula help
Ken,
Thanks so much, I spent 7 hours trying to figure this out by myself and within 45 min you had solved this. "THANKS" - Pat. "Ken Hudson" wrote: Hi Pat, Try these formulas: In M1: =SUM(IF(D1=0, H1,0)+IF(E1=0, I1,0)+IF(F1=0, J1,0)+IF(G1=0, K1,0)) In O1: =SUM(IF(AND(D1=0, H10),1,0)+IF(AND(E1=0, I10),1,0)+IF(AND(F1=0, J10),1,0)+IF(AND(G1=0, K10),1,0)) -- Ken Hudson "Pat" wrote: This is what my data is: B= BLIND SCORE TOTALS GM1 GM2 GM3 GM4 GM1 GM2 GM3 GM4 SERIES BOWLED # OF GAMES D E F G H I J K M O B B 0 0 100 100 150 150 300 2 D THRU G WILL EITHER = "B" OR "0" H THRU K WILL EITHER = 0 OR 0 I NEED "M" TO ONLY TOTAL VALUE OF "H" THRU "K" ONLY WHEN "D" THRU "G" = 0 I NEED "O" TO ONLY BE +1 EACH TIME "H" THRU "K" 0 & "D" THRU "G" = 0 I know what I want but, not even sure if I am explaining it correctly. PLEASE HELP ME. Thanks, Pat |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
complicated multi column formula help
Ken,
I spent 7 hours trying to get this and you had it in less than 45 min after I sent my question. It works great. THANKS - Pat "Ken Hudson" wrote: Hi Pat, Try these formulas: In M1: =SUM(IF(D1=0, H1,0)+IF(E1=0, I1,0)+IF(F1=0, J1,0)+IF(G1=0, K1,0)) In O1: =SUM(IF(AND(D1=0, H10),1,0)+IF(AND(E1=0, I10),1,0)+IF(AND(F1=0, J10),1,0)+IF(AND(G1=0, K10),1,0)) -- Ken Hudson "Pat" wrote: This is what my data is: B= BLIND SCORE TOTALS GM1 GM2 GM3 GM4 GM1 GM2 GM3 GM4 SERIES BOWLED # OF GAMES D E F G H I J K M O B B 0 0 100 100 150 150 300 2 D THRU G WILL EITHER = "B" OR "0" H THRU K WILL EITHER = 0 OR 0 I NEED "M" TO ONLY TOTAL VALUE OF "H" THRU "K" ONLY WHEN "D" THRU "G" = 0 I NEED "O" TO ONLY BE +1 EACH TIME "H" THRU "K" 0 & "D" THRU "G" = 0 I know what I want but, not even sure if I am explaining it correctly. PLEASE HELP ME. Thanks, Pat |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
complicated multi column formula help
"T. Valko" - Thanks to you also, I tried your formula's also, and they work
also, As I told Ken , I spent Hours trying to figure this out for my self. THANKS - Pat "T. Valko" wrote: For Series Bowled: =SUMIF(D2:G2,0,H2:K2) For # of Games: =SUMPRODUCT(--(ISNUMBER(D2:G2)),--(D2:G2=0),--(H2:K20)) Biff "Pat" wrote in message ... This is what my data is: B= BLIND SCORE TOTALS GM1 GM2 GM3 GM4 GM1 GM2 GM3 GM4 SERIES BOWLED # OF GAMES D E F G H I J K M O B B 0 0 100 100 150 150 300 2 D THRU G WILL EITHER = "B" OR "0" H THRU K WILL EITHER = 0 OR 0 I NEED "M" TO ONLY TOTAL VALUE OF "H" THRU "K" ONLY WHEN "D" THRU "G" = 0 I NEED "O" TO ONLY BE +1 EACH TIME "H" THRU "K" 0 & "D" THRU "G" = 0 I know what I want but, not even sure if I am explaining it correctly. PLEASE HELP ME. Thanks, Pat |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
complicated multi column formula help
You're welcome. Thanks for the feedback!
Biff "Pat" wrote in message ... "T. Valko" - Thanks to you also, I tried your formula's also, and they work also, As I told Ken , I spent Hours trying to figure this out for my self. THANKS - Pat "T. Valko" wrote: For Series Bowled: =SUMIF(D2:G2,0,H2:K2) For # of Games: =SUMPRODUCT(--(ISNUMBER(D2:G2)),--(D2:G2=0),--(H2:K20)) Biff "Pat" wrote in message ... This is what my data is: B= BLIND SCORE TOTALS GM1 GM2 GM3 GM4 GM1 GM2 GM3 GM4 SERIES BOWLED # OF GAMES D E F G H I J K M O B B 0 0 100 100 150 150 300 2 D THRU G WILL EITHER = "B" OR "0" H THRU K WILL EITHER = 0 OR 0 I NEED "M" TO ONLY TOTAL VALUE OF "H" THRU "K" ONLY WHEN "D" THRU "G" = 0 I NEED "O" TO ONLY BE +1 EACH TIME "H" THRU "K" 0 & "D" THRU "G" = 0 I know what I want but, not even sure if I am explaining it correctly. PLEASE HELP ME. Thanks, Pat |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Forming a binary column from a more complicated column of diagnostic codes | Excel Discussion (Misc queries) | |||
| match in multi-column and multi-row array | Excel Discussion (Misc queries) | |||
| Multi Column Sum and conditions | Excel Worksheet Functions | |||
| multi column of sumif | Excel Worksheet Functions | |||
| Multi-Column Printing | Excel Discussion (Misc queries) | |||
Linear Mode
