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

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.

CodePudding user response：

Well, try this:

=COUNTIF(B:B,"*xxx*")

The * is the wildcard so "xxx" is found in any position. You can edit it to match your data which i did not check as I based this on your comment.

CodePudding user response：

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

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