Sum multiple colums of data

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

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 ## ## ## ## ## ##
 
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
 
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.
 
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)
 
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
 
Back
Top