Index/Match Question

[Guest]

I have the following car sales spreadsheet:
How could I add each car with each department and in each month?

Dept A Dept A Dept A Dept B Dept B Dept B
Jan Feb Mar Jan Feb Mar
Audi 550 230 650 85 320 300
Chev 350 260 780 800 250 254
Buick 420 312 240 87 99 187
Chev 147 62 174 23 86 80
Audi 94 70 209 214 67 68

Thanks in advance!

 

[Biff]

As long as your dept and month headers follow that linear pattern:

A10 = make
B10 = dept
C10 = month

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=SUMIF(A3:A7,A10,INDEX(B3:G7,,MATCH(B10&C10,B1:G1&B2:G2,0)))

Here's a screencap:

http://img165.imageshack.us/img165/627/sumifbk1.jpg

Biff

 

[Herbert Seidenberg]

Pivot Table > Multiple consolidation ranges
Range: Select all data except Department row.
Finish.

 

[Guest]

Thank you both, I didn't want to use pivot tables.
Biff that formula worked great.

Gingit

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Thank you both, I didn't want to use pivot tables.
Biff that formula worked great.

Gingit