I'm out of my depth! Please Help!

julesrapley

New Member
Joined
Oct 16, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi all - not a coder - not an anything other than someone trying to make my life a little easier at work. Here's my problem (basing this on just one person and one row, I can size up later)

1st sheet (Db) has name of person, start date of course (F2) then column for if they completed (yes, no, in progress, waiting) (H2)
3rd sheet (Data) has the 2020/21 quarter dates eg. B7 is 01/04/2020 and B8 is 30/06/2020, Brit date format)
2nd sheet has the stats I need to input = Total courses Q1, etc.
So .... =COUNTIFS(Db!F2,">="&Data!B7,Db!F2,"<="&Data!B8,Db!H2,"<>no") will tell me if the one person has had a single course between specific dates with only No as being not counted

My problem is the same person can start multiple courses within the same quarter (start dates at cells L2, R2, X2 and AD2 with completion question at cells N2, T2, Z2 and AF2)

What is the formula for giving the total number of courses completed by that person within that quarter?

Hope that made sense!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
From what I understand, your data is not well organized; therefore if the same information can be found on several groups of columns you have to use COUNTIFS(1st Group)+COUNTIF(2nd Group) + COUNTIF(3rd Group) + Etc Etc
Or you put the information for a single person + single course on one line, and at that point one single COUNTIF will do the job, dealing with dates, names of persons, Code of the course

If you can share a sample of your data (a real workbook with sample data) maybe we could make some tangible examples

Bye
 

julesrapley

New Member
Joined
Oct 16, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
So, here you go ... Details go into Db, I reference the quarters from Data and I need to have the relevant information in the Stats sheet.

1. I need the total clients who've started courses within the specified quarter and who do not have a "No" in Completed column(s) and there are 5 training sections
2. I need total no. of courses started within the specified quarter that do not have a "No" in the Completed column(s)

The Db will be added to all the time as there are no specific start dates for courses or for when someone will get added to the list.

I appreciate that it must appear very untidy and poorly executed but I'm trying my best with limited knowledge so please don't get technical on me.

Thanks
 

Attachments

  • Db Screenshot.jpg
    Db Screenshot.jpg
    193.4 KB · Views: 6
  • Stats.jpg
    Stats.jpg
    80.8 KB · Views: 6
  • Data.jpg
    Data.jpg
    73.1 KB · Views: 5

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
If you can share a sample of your data (a real workbook with sample data) maybe we could make some tangible examples
Your images could be useful for us to recreate your workbook; but that would be a good way to waste our time, as you already have the workbook and can easily remove any confidential information.
To share a workbook you have to upload it to a filesharing service; the easiest to use (in my opinion) is filedropper.com. You will then publish the download-link in your next message.
Bye
 

julesrapley

New Member
Joined
Oct 16, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Your images could be useful for us to recreate your workbook; but that would be a good way to waste our time, as you already have the workbook and can easily remove any confidential information.
To share a workbook you have to upload it to a filesharing service; the easiest to use (in my opinion) is filedropper.com. You will then publish the download-link in your next message.
Bye
That would be great. However, I'm working on an encrypted corporate laptop that will not allow me to use or access that kind of service. But I understand if you don't want to assist.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,245
Messages
5,641,070
Members
417,192
Latest member
choomkey

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
Top