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 ## ## ## ## ## ##

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 ## ## ## ## ## ##

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 ## ## ## ## ## ##

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 ## ## ## ## ## ##

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 ## ## ## ## ## ##

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