Can I write an Excel sumif or sumproduct that sums cells in column based on identifying strings in a reference column?

Question

I'm trying to build a summary table in excel from a source table where the columns and rows of the summary table are strings within the source table:

The source data looks like this:

and the summary table looks like this:

With the wrinkle that there may be multiple data rows with "Paris Day 1" as the description, so the summary table cell Paris x Day1 needs to sum all Paris Day 1 rows.

The logic of this doesn't seem overly complex but I'm not good enough with Excel's string functions to be able to work this out. Something involving SUMPRODUCT, ISNUMBER and SEARCH maybe?

If anyone can help that would be appreciated.

Answer 1

You can use wildcard with SUMIFS() function. Try-

=SUMIFS($B$1:$B$9,$A$1:$A$9,"*" & $D3 &"*",$A$1:$A$9,"*" & E$2 & "*")