Home > Back-end >  Can I write an Excel sumif or sumproduct that sums cells in coloumn based on identifying strings in
Can I write an Excel sumif or sumproduct that sums cells in coloumn based on identifying strings in

Time:12-02

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:

So the source data looks like:
enter image description here

and the summary table looks like:"
enter image description here
...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*")

enter image description here

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 & "*")

enter image description here

  • Related