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

so trying sumif inside sumif. here scenario: (note item type , currency 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 make summary sheet can sumif respect currency vs. item type, summary sheet like:

               cad          usd hats          $5.00     $4.00             shoes         $3.00     $4.00  gloves        $0.00        $2.00 

i can't figure out how 2 conditions. i've tried using sumif inside sumif following:

= sumif(a2:a7,"hats",sumif(b3:b7,"cad",c2:c7)) 

but didn't work, not expected to. drawing blanks how excel.

you can turn data excel table (excel 2007 , later) selecting 1 cell , clicking insert > table. after that, select cell in table , click insert > pivot table.

drag itemtype rows panel, drag currency columns panel , amount values panel.

you can add new rows of data @ time below table. new rows belong table , available pivot table. right-click pivot table , refresh include newly added data.

for one-off or less dynamic solution, can use sumproduct. prepare headers rows , columns , apply formula along these lines.

=sumproduct($c$2:$c$7*($a$2:$a$7=$a11)*($b$2:$b$7=b$10)) 

or sumifs

=sumifs($c$2:$c$7,$a$2:$a$7,$a11,$b$2:$b$7,b$10) 

see screenshot cell locations.