Sum up number of times one OR other criteria is present ONCE per row

**TonyTheTigerTTT** · 11-02-2023, 04:18 AM

Hi

Im reaching out and hoping someone with far more experiance than me can help me out with this issue.

Have an excel with a few thousand rows, in column Q there is information that has this kind of format;
"bytasida;nagraflersaker;avslutaMedlemskap;lemyscenter;ByUttrade;V82;$session.params.otherQuestions"
or
"lonrattning;lemyscenter;V02;smsSentNo;$session.params.otherQuestions"

I want to be able to count how many times "avslutaMedlemskap" and "ByUttrade" exists in the Q column, but if it both are present on the same row as in the upper example then it only suppose to only count it once.

So all in total but only count max once per row.

**Pepe Le Mokko** · 11-02-2023, 04:24 AM

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**Glenn Kennedy** · 11-02-2023, 04:25 AM

Please post a small sample file showing what you have and some expected answers. See yellow banner (top) for guidelines regarding sample files.

**Glenn Kennedy** · 11-02-2023, 04:29 AM

This will be close to what you want:

=LET(Z,A2:A20,ROWS(FILTER(Z,(ISNUMBER(SEARCH("A",Z))+ISNUMBER(SEARCH("B",Z))))))

Change the bit in red to suit your raw data and the bits in green to match the terms you're searching for.

**HansDouwe** · 11-02-2023, 05:05 AM

Another option: Please try:

Formula:

Please Login or Register  to view this content.

**TonyTheTigerTTT** · 11-02-2023, 05:31 AM

Hey thanks for the welcome.

Hope this sample is up to standards.

**AliGW** · 11-02-2023, 05:36 AM

What is the problem with the two suggestions already offered?

**TonyTheTigerTTT** · 11-02-2023, 06:09 AM

Thanks Glenn and Hans, i have no idea what differentes the formulas you guys did but both works, thanks for the help!

**HansDouwe** · 11-02-2023, 07:16 AM

You are Welcome!

Thanks for the feedback and rep

. Glad to have helped.

**TonyTheTigerTTT** · 11-03-2023, 03:00 AM

How would i add into the formuals to not count one row if a given value is present. So count if "1" or "2" is there but skip that count if "3" is there?

EDIT: let me know if i should start another thread instead of continue on this one.

**AliGW** · 11-03-2023, 03:03 AM

Continuing here is fine, as it's a tweak you need.

**Glenn Kennedy** · 11-03-2023, 04:31 AM

A modification of mine, excluding "Q".

Sum up number of times one OR other criteria is present ONCE per row

LinkBack

Thread Tools

Rate This Thread

Display

Sum up number of times one OR other criteria is present ONCE per row

Re: Sum up number of times one OR other criteria is present ONCE per row

Re: Sum up number of times one OR other criteria is present ONCE per row

Re: Sum up number of times one OR other criteria is present ONCE per row

Re: Sum up number of times one OR other criteria is present ONCE per row

Re: Sum up number of times one OR other criteria is present ONCE per row

Re: Sum up number of times one OR other criteria is present ONCE per row

Re: Sum up number of times one OR other criteria is present ONCE per row

Re: Sum up number of times one OR other criteria is present ONCE per row

Re: Sum up number of times one OR other criteria is present ONCE per row

Re: Sum up number of times one OR other criteria is present ONCE per row

Re: Sum up number of times one OR other criteria is present ONCE per row

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Search for two texts and return two times another cell if present and return 1 times

find 1 value in a table and count the number of times another in present

How to sync between two columns and present the number of times value appears on the colum

[SOLVED] Count number times same value is present in a single Row of a larger range.

[SOLVED] Lookup and count number of times a number is present???

Count number of times value is present i area.

Excel 2007 : counting the number of times a particular month is present in a column

Bookmarks

Bookmarks

Posting Permissions