IFSUM and IFSUMS - which to use and how

[RLD]

I have 2 sheets. They are as follows:

Sheet1:
A B C
1 Make Type Qty
2 Toyota Hybrid 20
3 Ford Hybrid 68
4 Nissan Sedan 95
5 Toyota Hybrid 11
6 Dodge Truck 15
7 Toyota Sedan 21
8 Ford Sedan 0

Sheet2:
A B C
1 Make Qty Hybrids Qty Sedans
2Toyota
3 Ford

I need B2 and C2 of Sheet2 to sum up the total number of matching data from
Sheet1. In other words, how do I get Sheet2 B2 to sum up the total number of
Toyota Hybrids in Sheet 1?

 

[Dave Peterson]

This is a perfect situation to learn about pivottables. It makes this kind of
thing much easier (especially when new makes/types are added).

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

=======
One more point. You'll want to make sure that the pivottable range includes all
the data you need--if/when you add more items.

Debra Dalgleish shares instructions for this:
http://contextures.com/xlNames01.html#Dynamic

 

[Luke M]

Sheet2, B2 formula is
=SUMPRODUCT(--('Sheet1'!$A$2:$A$10=$A2),--('Sheet1'!$B$2:$B$10="Hybrid"),('Sheet1'!$C$2:$C$10))

Similarly, C2 formula is:
=SUMPRODUCT(--('Sheet1'!$A$2:$A$10=$A2),--('Sheet1'!$B$2:$B$10="Sedan"),('Sheet1'!$C$2:$C$10))

Copy down as needed

 

[Teethless mama]

Try Pivot table

 

[Paul C]

dWith two conditons "Toyota" and "Hybrid" you nee to use SUMIFS

For this function the sum range leads off, then each range and criteria

for B2
=SUMIFS(Sheet1!$C$2:$C$8,Sheet1!$A$2:$B$8,A2,Sheet1!$B$2:$B$8,"Hybrid")

If you change B1 to just Hybrid and the C1 with Sedan you can relplace the
"Hybrid" with B$1 and the formula will copy both down rows and over columns.