Find values according to criteria and count instances

[ollyhughes1982]

Hi all,

I have a worksheet (‘All Completed VS’) with all of the parkruns at which I have completed volunteering stints and which role I undertook at each one.

What I am trying to do is populate columns AA to CB in the ‘Completed Unique VS E(V)’ worksheet (below).

This worksheet shows all of the unique events at which I have undertaken volunteering roles. In columns AA to CB I have two columns for each type of the 27 possible volunteering roles available. In the first column for each I want to show a ‘1’ if I have completed that role at that particular event (conditional formatting is set to fill the cell with colour). In the second column for each type of role, I want to show the number of times it has been completed at that venue.

Link to file is here: parkrun - Volunteering Example.xlsx

Thanks in advance,

Olly.

 

[etaf]

I thought this had been answered here

Possibly countifs for two helper columns

Hi all, I have the ‘All Completed VS’ worksheet (screenshot below and link attached). The worksheet shows all of the parkruns at which I have volunteered and the volunteering role that I completed at each. I am looking to do some calculations on this worksheet, but cannot figure out how to...

www.mrexcel.com

is this different

 

[ollyhughes1982]

Hi. yes, this is a different worksheet. All I need here is a 1 if it has been done at the particular event and then a count in the second column. I need to add this grid-type layout in order to be able to solve the final two worksheets that I have

 

[ollyhughes1982]

In this one, it will need to search the name of the role (e.g. 'Barcode Scanning' for cell AA1) and check if it appears anywhere against 'Newport parkrun' in the 'All Completed VS' worksheet. Then, the second column counts how many times that role has been completed at that event. Again, searching on the role name 'Barcode Scanning' appearing anywhere against 'Newport parkrun' in the 'All Completed VS' worksheet, but this time counting the total instances.

This grid type structure is the only way I can think of that will be able to solve points 3 and 4 in my other original post.

Thanks again.

 

[etaf]

i'm out now rest of day , But I dont see where to lookup if you have done this event
I maybe missing something here, so apolgies if thats the case

All Completed VS - VER-I. has the Locations Events just as a number
All Completed VS - VRE-I. has the roles - just as a number

How do I know barcode scanning has been done at Newport parkrun ?

And how do i know how many times it has been done at Newport parkrun ?

Again I suspect a COUNTIF() or COUNTIFS() and a SUMIF() should work

Also points 3 and 4 , i assume are the ones mentioned on another thread

 

[ollyhughes1982]

This worksheet only shows the unique events that I have done, whereas the 'All Completed VS' worksheet shows all the parkruns. So, in this sheet I just want to be looking up if I have done a certain role against each unique event that I have done, and how many times. I think this will then start to give me what I need for 3 and 4 on the other thread. This is different from 1 and 2. Thanks.

 

[ollyhughes1982]

I have now found a solution for column AA, using the following formula:

=COUNTIFS('All Completed VS'!$C$4:$C$2003,B4,'All Completed VS'!$E$4:$E$2003,'Volunteer Roles'!$B$3)

I am however struggling with the second column (AB).

Thanks again.

 

[ollyhughes1982]

I have now resolved this, with:

Column AA:
=IF(B4<>"",IF(COUNTIFS('All Completed VS'!$C$4:$C$2003,B4,'All Completed VS'!$E$4:$E$2003,'Volunteer Roles'!$B$3)=0,"",1),"")

Column AB:
=IF(B4<>"",IF(COUNTIFS('All Completed VS'!$C$4:$C$2003,B4,'All Completed VS'!$E$4:$E$2003,'Volunteer Roles'!$B$3)=0,"",COUNTIFS('All Completed VS'!$C$4:$C$2003,B4,'All Completed VS'!$E$4:$E$2003,'Volunteer Roles'!$B$3)),"")

Thanks.