d2jsp
d2jsp Forums > Off-Topic > Programmer's Haven > Java, Visual Basic, VBScript > Fg For Excel Help!
Add Reply New Topic New Poll
memoorhouse
Group: Member
Posts: 13,354
Joined: Jan 9 2008
Gold: 2,314.00
Nov 24 2022 06:32am
background: I do not have formal training in excel, I've learned a fair bit about formulas via google and raw anger, and I do like it, but no actual training.

issue:

I track a large amount of people (say 3000 but the number fluctuates regularly) that are required to do something once a fiscal year on or before the month they were born.

the workbook I'm working from has a column with their date of birth, and a column with the date they completed the required task (which is blank until they complete it, we use a new sheet each FY)

what I want is to write a couple formulas that result in a legend with the following stats:

Amount due (just sum of how many people have birth months from Apr - the current month (obviously this formula needs to work right in jan-mar timeframe as well...)
Amount Complete (simple COUNTA on the completed column, dont need a formula here)
Amount Overdue (number of people whose birth month is prior to this month (this fiscal year not calendar), that have nothing in the "date completed" column)
Amount Outstanding FY22/23 (the total number of people that haven't done one this FY, I know I could count-if-blank, but how do I know what to put for an end range when people are coming and going, the "bottom" of the list moves? assuming there's a fancy way of only going to the bottom of your data but not sure what that is)

as you can probably tell from my wording, I'm in way over my head here, fg to someone who can help! (Unfortunately, I won't be able to use a formula if I can't explain how it works, that being said once I see it and can google the parts of it, I'll probably be able to make sense of it, but explain like I'm 5 if you can lol)
r4f
Group: Member
Posts: 3,110
Joined: May 30 2021
Gold: 10.00
Nov 25 2022 08:56am
I am struggling to follow. Are you able to post an example table with dummy data of what you’re trying to achieve? Sounds like you know the desired results and just need some guidance on getting there.
quiknen
Group: Member
Posts: 5
Joined: Sep 29 2022
Gold: 1.00
Nov 25 2022 03:24pm
I'm going to assume that you could create this by doing a combination of a couple SUMIF's by setting VLOOKUPs first to establish the data?... and then just use COUNT in the spots you want?
Go Back To Java, Visual Basic, VBScript Topic List
Add Reply New Topic New Poll