0

I’d like to tally up rows that contain a certain text string in my PivotTable of studies.

This is the formula I use in the Insert Calculated Field dialog: =ISNUMBER(FIND("🇬🇧",internalName))*numberOfBooked

hoping to get a sum of the booked participants in Great Britain.

But it doesn’t seem to work, it always sums it up to 0.

The respective dialog in Excel showing the formula mentioned above

Is it impossible to use the FIND-function in calculated fields? Or is there another mistake I made?

When I use the same formula in a manually created table, it works like a charm.

Improve this question
4
  • I can't quite tell, but it looks like the GB is a different font/unicode (maybe?) than the rest. Are you simply typing "GB" or are you pasting from somewhere? Commented Oct 21 at 19:53
  • 1
    Are you trying to sum the numberOfBooked where internalName contains GB? If you add the data to the Data Model (tick box at bottom when creating the pivot table) and use Power Pivot you could create a dax measure to do this: =CALCULATE(SUM(Table1[numberOfBooked]), FILTER(Table1, NOT(ISBLANK(Table1[internalName])) && FIND("GB",Table1[internalName],1,0)>0)). If you just want a count of rows change the SUM(Table1[numberOfBooked]) bit to COUNTROWS(Table1). Commented Oct 22 at 8:22
  • Thanks Bruce, yes it’s unicode, just as any text in Excel (: The FIND call with the emoji works well. Thanks Darren, that looks promising! I guess I need to dig a bit into Power Pivot. Commented Oct 22 at 8:56
  • This should help with Power Pivot: support.microsoft.com/en-us/office/… Commented Oct 22 at 9:24
Related questions
392
How can I return pivot table output in MySQL?
766
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
141
Simple Pivot Table to Count Unique Values
Load 5 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.