Index/Match Question
 

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!

Index/Match Question
 

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

"Gingit" wrote in message
...
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!

Index/Match Question
 

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

Index/Match Question
 

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

Gingit

"Biff" wrote:

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

"Gingit" wrote in message
...
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!

Index/Match Question
 

You're welcome. Thanks for the feedback!

Biff

"Gingit" wrote in message
...
Thank you both, I didn't want to use pivot tables.
Biff that formula worked great.

Gingit

"Biff" wrote:

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

"Gingit" wrote in message
...
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!