advance countifs

[End3r]

Hello,

i am having trouble getting my formula to work properly.

=COUNTIFS(table_name_first_table[tablerow_name)];"*";table_name_first_table[tablerow_name)];"name";tablename[tableRow_description];"*new*")

So, i want to count how many times name of the person shows up given two criterias. First criteria is "name". Second criteria is that i need the word "new" to appear in row "description". And this works perfectly but i also have another row where i do same counting but for word "old". This description can have, due to history of description both "new" and "old" and i need to count only the first one that appears. So, in table row "new" if you first get to the word "old" then count 0, otherwise 1. If in the row "old" if the first word is "new" then count 0, otherwise count 1.

Thanks for all your help !

E

 

[steve the fish]

Re: advance countifs with a twist

Paste a comprehensive example of your data showing what you have and expected results.

 

[End3r]

Re: advance countifs with a twist

Here you go !
Thanks for helping out.

https://drive.google.com/open?id=16x7d2beC3FfHemeuL88nBvN8bD6m4UZQ

so, as you can see, it double counts if in description it has both "nova" and "retention".

 

[steve the fish]

Re: advance countifs with a twist

Ok you could use these:

=COUNTIFS(table1[Name],A2,table1[Description],"*nova*",table1[ID],"1*")
=SUM(COUNTIFS(table1[Name],A2,table1[Description],"*nova*",table1[ID],{"6*","8*"}))
=COUNTIFS(table1[Name],A2,table1[Description],"*retention*",table1[Description],"<>*nova*",table1[ID],"1*")
=SUM(COUNTIFS(table1[Name],A2,table1[Description],"*retention*",table1[Description],"<>*nova*",table1[ID],{"6*","8*"}))

If nova and retention are present in the cell then nova takes precendence. They now will not be counted in the retention column.

 

[End3r]

Re: advance countifs with a twist

Ok you could use these:

=COUNTIFS(table1[Name],A2,table1[Description],"*nova*",table1[ID],"1*")
=SUM(COUNTIFS(table1[Name],A2,table1[Description],"*nova*",table1[ID],{"6*","8*"}))
=COUNTIFS(table1[Name],A2,table1[Description],"*retention*",table1[Description],"<>*nova*",table1[ID],"1*")
=SUM(COUNTIFS(table1[Name],A2,table1[Description],"*retention*",table1[Description],"<>*nova*",table1[ID],{"6*","8*"}))

If nova and retention are present in the cell then nova takes precendence. They now will not be counted in the retention column.

Thanks alot. Much appriciated.

Problem I have with this is that i dont need "nova" to have precendence, precendence has something that is first mentioned. Being retention or nova.

E

 

[Aladin Akyurek]

Re: advance countifs with a twist

What are the expected values?

 

[End3r]

Re: advance countifs with a twist

Expected value is that when "Description is checked" that it counts only 1 value and that is the first one that is mentioned. "nova" or "retention".
For example: in this text it should count only "nova" : Lorem ipsum nova dolor retention sit amet. in this one it should only count "retention": Lorem retention ipsum dolor nova sut amet.
Thanks for your help !

What are the expected values?

 

[Aladin Akyurek]

Re: advance countifs with a twist

You have, shown partly:

Book1
	A	B	C
1	Name	ID	Description
2	John Smith	8006982	Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nova Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non.
3	John Smith	8007032	Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nova Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non. Retention
4	John Smith	8007157	Lorem ipsum dolor sit amet, consectetur adipiscing elit. Retention Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non. Nova
5	John Smith	8007316	Lorem ipsum dolor sit amet, consectetur adipiscing elit. Retention Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non.
database

Book1
	A	B	C	D	E	F	G	H
1	2018	Nova Mob	Nova Fix	Nova Mob + Fix	retention Mob	retention Fix	retention Mob + Fix	TOTAL
2	John Smith
3	John Doe
matrix

Are we counting the occurrences of Nova Mob in database for John Smith?

 

[End3r]

Re: advance countifs with a twist

Ok, since they all start, in your example below, with number "8" they are all FIX.
Nova Mob in database start with number "1" and they have "Nova" in description as first word appearing between words "nova" and "retention".

so the count would be as follows :

	A	B	C	D	E	F	G	H
1	2018	Nova Mob	Nova Fix	Nova Mob + Fix	retention Mob	retention Fix	retention Mob + Fix	TOTAL
2	John Smith		2			2
3	John Doe

<tbody>
</tbody>

matrix​

You have, shown partly:

	A	B	C
1	Name	ID	Description
2	John Smith	8006982	Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nova Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non.
3	John Smith	8007032	Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nova Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non. Retention
4	John Smith	8007157	Lorem ipsum dolor sit amet, consectetur adipiscing elit. Retention Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non. Nova
5	John Smith	8007316	Lorem ipsum dolor sit amet, consectetur adipiscing elit. Retention Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non.

<tbody>
</tbody>

database​

	A	B	C	D	E	F	G	H
1	2018	Nova Mob	Nova Fix	Nova Mob + Fix	retention Mob	retention Fix	retention Mob + Fix	TOTAL
2	John Smith
3	John Doe

<tbody>
</tbody>

matrix​

Are we counting the occurrences of Nova Mob in database for John Smith?

 

[Aladin Akyurek]

Re: advance countifs with a twist

1. You don't have to quote so extensively. I posted a portion of your data from the link you provided, so it's not my example.

2. I don't understand at all what this explanation means: "Nova Mob in database start with number "1" and they have "Nova" in description as first word appearing between words "nova" and "retention"." Would you just give the counts for the portion I have posted from your data? Note. I didn't see any nova mob in your database...