Excel formula to return value if two conditions are matched

0
votes

I need a formula that can be pasted into cells B2:D5 in the sheet displayed in the image below. The formula should see if the value for the row in column A matches a color in row G and if the value for the column in row 1 matches a animal in row H. For all cells without a two part match it should return a blank "". If there is a two way match then it returns the numerical value in column I

enter image description here

1
excelexcel-formula
SUMIFS and COUNTIFS, or more inspiration here. – BigBen

1 Answers

1
votes

Put this formula in B2, then copy across and down:

=IF(SUMIFS($I$2:$I$7,$G$2:$G$7,$A2,$H$2:$H$7,B$1)=0,"",SUMIFS($I$2:$I$7,$G$2:$G$7,$A2,$H$2:$H$7,B$1))