SUMIF inside a SUMIF doesn't seem to be possible, anyways around it?

Question

So I am trying to do a SUMIF inside a SUMIF. Here is the scenario: (note that item type and currency are both lists.):

Itemtype    Currency        Amount
hats         CAD            $3.00
hats         CAD            $2.00
Shoes        USD            $4.00
hats         USD            $4.00
shoes        CAD            $3.00
gloves       USD            $2.00

I want to make a summary sheet where I can SUMIF with respect to the currency vs. item type, so my summary sheet would look something like:

               CAD          USD
hats          $5.00     $4.00            
Shoes         $3.00     $4.00 
gloves        $0.00        $2.00

I can't figure out how to do that with two conditions. I've tried using a SUMIF inside a SUMIF like the following:

= SUMIF(A2:A7,"hats",SUMIF(B3:B7,"CAD",C2:C7))

but that didn't work, not that I expected to. I am drawing blanks for how to do this with Excel.

Answer 1

You can turn the data into an Excel table (Excel 2007 and later) by selecting just one cell and then clicking Insert > Table. After that, select any cell in the table and click Insert > Pivot table.

Drag the ItemType into the rows panel, drag the Currency into the columns panel and the Amount into the values panel.

You can add new rows of data at any time just below the table. New rows will belong to the table and will be available to the Pivot table. Then right-click the pivot table and refresh it to include the newly added data.

For a one-off or less dynamic solution, you can use Sumproduct. Prepare the headers for rows and columns and then apply the formula along these lines.

=SUMPRODUCT($C$2:$C$7*($A$2:$A$7=$A11)*($B$2:$B$7=B$10))

or with Sumifs

=SUMIFS($C$2:$C$7,$A$2:$A$7,$A11,$B$2:$B$7,B$10)

See screenshot for cell locations.