advance countifs

E

End3r

New Member
Joined
May 9, 2017
Messages
14
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Re: advance countifs with a twist :)

Paste a comprehensive example of your data showing what you have and expected results.
 
Upvote 0
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.
 
Upvote 0
Re: advance countifs with a twist :)

steve the fish said:
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.
Click to expand...

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
 
Upvote 0
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 !
:)

Aladin Akyurek said:
What are the expected values?
Click to expand...
 
Last edited:
Upvote 0
Re: advance countifs with a twist :)

You have, shown partly:



Book1
ABC
1NameIDDescription
2John Smith8006982Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nova Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non.
3John Smith8007032Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nova Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non. Retention
4John Smith8007157Lorem ipsum dolor sit amet, consectetur adipiscing elit. Retention Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non. Nova
5John Smith8007316Lorem ipsum dolor sit amet, consectetur adipiscing elit. Retention Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non.
database



Book1
ABCDEFGH
12018Nova MobNova FixNova Mob + Fixretention Mobretention Fixretention Mob + FixTOTAL
2John Smith
3John Doe
matrix


Are we counting the occurrences of Nova Mob in database for John Smith?
 
Upvote 0
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 :

ABCDEFGH
John Smith
John Doe

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]Nova Mob[/TD]
[TD="align: center"]Nova Fix[/TD]
[TD="align: center"]Nova Mob + Fix[/TD]
[TD="align: center"]retention Mob[/TD]
[TD="align: center"]retention Fix[/TD]
[TD="align: center"]retention Mob + Fix[/TD]
[TD="align: center"]TOTAL[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
matrix​

Aladin Akyurek said:
You have, shown partly:


ABC
NameIDDescription
John SmithLorem ipsum dolor sit amet, consectetur adipiscing elit. Nova Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non.
John SmithLorem ipsum dolor sit amet, consectetur adipiscing elit. Nova Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non. Retention
John SmithLorem ipsum dolor sit amet, consectetur adipiscing elit. Retention Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non. Nova
John SmithLorem ipsum dolor sit amet, consectetur adipiscing elit. Retention Pellentesque et dui lorem. Curabitur rhoncus arcu turpis, id tempus purus rutrum non.

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8006982[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8007032[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8007157[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8007316[/TD]

</tbody>
database​

ABCDEFGH
John Smith
John Doe

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]Nova Mob[/TD]
[TD="align: center"]Nova Fix[/TD]
[TD="align: center"]Nova Mob + Fix[/TD]
[TD="align: center"]retention Mob[/TD]
[TD="align: center"]retention Fix[/TD]
[TD="align: center"]retention Mob + Fix[/TD]
[TD="align: center"]TOTAL[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
matrix​

Are we counting the occurrences of Nova Mob in database for John Smith?
Click to expand...
 
Last edited:
Upvote 0
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...
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA for Cut, move, paste, delete rows
Replies
6
Views
153
RForster6
R
A
  • Solved
COUNT multiple SUM COUNTIFS
Replies
6
Views
222
Peter_SSs
Peter_SSs
V
  • Question Question
COUNTIFS for 2 criteria
2
Replies
10
Views
404
VPLUMWOOD
V
N
  • Solved
Find next visible cell, add a formula and then fill column down
Replies
8
Views
356
MARK858
MARK858
X
  • Question Question
VBA for extracting specific data from PDF Docs
Replies
3
Views
645
xcellrodeo
X

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top