# Sum Columns by criteria

This is a discussion on Sum Columns by criteria within the Excel Questions forums, part of the Question Forums category; 2004 2005 2006 Type 0 0 0 EQ 0 0 0 FI 1 0 0 SC 0 0 0 FI ...

1. ## Sum Columns by criteria

2004 2005 2006 Type
0 0 0 EQ
0 0 0 FI
1 0 0 SC
0 0 0 FI
0 0 0 EQ
0 0 0 EM
0 0 0 SC
0 0 0 EQ
0 0 0 EQ
0 1 0 EQ
1 0 0 EQ
1 0 0 EQ

Hi - I'm sure this is easy but I'm have a very dumb afternoon.

I need to produce a summary table of above showing:

2004 2005 2006
EQ 2 1 0
FI 0 0 0
SC 1 0 0
EM 0 0 0

I've tried pivottables and countif but I can't get anything to work! help!!

2. ## Re: Sum Columns by criteria

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 G2H2I2G3H3I3G4H4I4G5H5I5 =

A
B
C
D
E
F
G
H
I
1
200420052006Type**200420052006
2
000EQ*EQ210
3
000FI*FI000
4
100SC*SC100
5
000FI*EM000
6
000EQ*****
7
000EM*****
8
000SC*****
9
000EQ*****
10
000EQ*****
11
010EQ*****
12
100EQ*****
13
100EQ*****
 Sheet1 *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

In G2 enter and copy across then down:

=SUMIF(\$D\$2:\$D\$13,\$F2,INDEX(\$A\$2:\$C\$13,0,MATCH(G\$1,\$A\$1:\$D\$1,0)))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•