| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum multiple colums of data
I'm trying to write a formula that will sum all columns by quarter, so it
will sum Jan-Mar by looking up "Q1" in row 1. Any thoughts are greatly appreciated. Thanks A B C D E F G 1 Q1 Q1 Q1 Q2 Q2 Q2 2 Jan Feb Mar Apr May Jun 3 #1 ## ## ## ## ## ## 4 #2 ## ## ## ## ## ## 5 #3 ## ## ## ## ## ## 6 #4 ## ## ## ## ## ## 7 #5 ## ## ## ## ## ## 8 #6 ## ## ## ## ## ## |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum multiple colums of data
=SUMPRODUCT((B1:M1="Q1")*B3:M8)
"Eric" wrote: I'm trying to write a formula that will sum all columns by quarter, so it will sum Jan-Mar by looking up "Q1" in row 1. Any thoughts are greatly appreciated. Thanks A B C D E F G 1 Q1 Q1 Q1 Q2 Q2 Q2 2 Jan Feb Mar Apr May Jun 3 #1 ## ## ## ## ## ## 4 #2 ## ## ## ## ## ## 5 #3 ## ## ## ## ## ## 6 #4 ## ## ## ## ## ## 7 #5 ## ## ## ## ## ## 8 #6 ## ## ## ## ## ## |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum multiple colums of data
In P1:P4 I entered: Q1, Q2, Q3, Q4 (no math pun intended!)
In Q1 I entered =SUMPRODUCT(($B$1:$M$1=P1)*$B$3:$M$8) Copied this down to Q4 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Eric" wrote in message ... I'm trying to write a formula that will sum all columns by quarter, so it will sum Jan-Mar by looking up "Q1" in row 1. Any thoughts are greatly appreciated. Thanks A B C D E F G 1 Q1 Q1 Q1 Q2 Q2 Q2 2 Jan Feb Mar Apr May Jun 3 #1 ## ## ## ## ## ## 4 #2 ## ## ## ## ## ## 5 #3 ## ## ## ## ## ## 6 #4 ## ## ## ## ## ## 7 #5 ## ## ## ## ## ## 8 #6 ## ## ## ## ## ## |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum multiple colums of data
Hi Eric
Try this array formula, press Ctl,Shift + Enter after entering the formula =SUM(IF((B1:G1="Q1"),B3:G8,0)) ====================== Pls click Yes if this has help you ====================== Thank You cheers, I may be able to provide you a solution, there would be better solutions from others. "Eric" wrote: I'm trying to write a formula that will sum all columns by quarter, so it will sum Jan-Mar by looking up "Q1" in row 1. Any thoughts are greatly appreciated. Thanks A B C D E F G 1 Q1 Q1 Q1 Q2 Q2 Q2 2 Jan Feb Mar Apr May Jun 3 #1 ## ## ## ## ## ## 4 #2 ## ## ## ## ## ## 5 #3 ## ## ## ## ## ## 6 #4 ## ## ## ## ## ## 7 #5 ## ## ## ## ## ## 8 #6 ## ## ## ## ## ## |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum multiple colums of data
Use Column Totals, and then SUMIF()
Col.Total =+SUM(B4:B9) =+SUM(C4:C9) =+SUM(D4:D9) =+SUM(E4:E9) =+SUM(F4:F9) =+SUM(G4:G9) Q1 Q1 Q1 Q2 Q2 Q2 Jan Feb Mar Apr May Jun #1 12 13 14 15 16 17 #2 22 23 24 25 26 27 #3 32 33 34 35 36 37 #4 42 43 44 45 46 47 #5 52 53 54 55 56 57 #6 62 63 64 65 66 67 Q1 =SUMIF($B$2:$G$2,$A11,$B$1:$G$1) Q2 =SUMIF($B$2:$G$2,$A12,$B$1:$G$1) "Eric" wrote: I'm trying to write a formula that will sum all columns by quarter, so it will sum Jan-Mar by looking up "Q1" in row 1. Any thoughts are greatly appreciated. Thanks A B C D E F G 1 Q1 Q1 Q1 Q2 Q2 Q2 2 Jan Feb Mar Apr May Jun 3 #1 ## ## ## ## ## ## 4 #2 ## ## ## ## ## ## 5 #3 ## ## ## ## ## ## 6 #4 ## ## ## ## ## ## 7 #5 ## ## ## ## ## ## 8 #6 ## ## ## ## ## ## |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum multiple colums of data
Results Screen:
Col.Total 222 228 234 240 246 252 Q1 Q1 Q1 Q2 Q2 Q2 Jan Feb Mar Apr May Jun #1 12 13 14 15 16 17 #2 22 23 24 25 26 27 #3 32 33 34 35 36 37 #4 42 43 44 45 46 47 #5 52 53 54 55 56 57 #6 62 63 64 65 66 67 Q1 684 Q2 738 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP for multiple colums | Excel Worksheet Functions | |||
| Using sumproduct to sum multiple colums | Excel Discussion (Misc queries) | |||
| Formating excel multiple paragraphs and colums using one source of data | Charts and Charting in Excel | |||
| lock multiple rows and colums ? | Excel Discussion (Misc queries) | |||
| sumif issues over multiple colums | Excel Worksheet Functions | |||
Hybrid Mode
