Countif date range AND Countif col in same row isnotblank.

[tnesseth]

Long time Reader First Time Pleader -

Guy I hope none has asked this same question exactly.
I have a 1400 fields in db object- i need the count of fields usage from the fields inception date.

Table one: Column A is the field Name then Created date field and then countif range from table two (all the records)

Field Name	Create Date	Usage from create
ABM_TIER_1__C	11/4/2020	1216
ACCOUNTID	2/11/2021	n/a
ACCOUNT_BU_VS_OPP_BU_MISMATCH__C	1/6/2020	5673

Table two (all the records and each fields contents)

ABM_TIER_1__C	ACCOUNTID	ACCOUNTID
4/16/2004​	T06:35:48.000Z	FALSE​
4/16/2004​	T06:35:48.000Z	FALSE​
4/16/2004​	T06:35:48.000Z	FALSE​

essentially i need a count of fields populated on or after their respective created dates.
so i can thoughtfully argue which fields can burn in eternal h..... you get the gist

You are true heroes!

 

[GlennUK]

Nope, I've read it twice, and it doesn't make sense. I think you ought to have another go at explaining what your table contents are (i.e. meaning), and what you need to do ... maybe explaining the logic in English.

 

[tnesseth]

Nope, I've read it twice, and it doesn't make sense. I think you ought to have another go at explaining what your table contents are (i.e. meaning), and what you need to do ... maybe explaining the logic in English.

Nope, I've read it twice, and it doesn't make sense. I think you ought to have another go at explaining what your table contents are (i.e. meaning), and what you need to do ... maybe explaining the logic in English.

Glenn!! THANK YOU FOR THE FEED BACK!!! much appreciated and apologies for the lack of clarity. although I am pretty sure its English (albeit not the Kings ...i will give you that good sir.) So utilization of fields report factoring in the date the field was created against the date a record was created.. if the field is blank it may be because it want there Example: if I run a rpt for field usage on a table w 1000 records and declare "no one is using this field, there is only one record where it is not blank. we need to DELETE IT) . and someone says to me: "I created that field this morning" I want to avoid that convo

Two tables

Table one: Field and the date it was created for use in the table (meta)

FIELD from table	FIELD created DATE	EXPECTED RESULTS - column 3 below has formula to return
field A	2/25/2020​	2 - from example below
field B​	6/2/2016​
field B​	1/1/2000​
field D	8/1/2011​
field E	8/1/2011​
	​

Table two: All the records created by users and all fields

Record create date	Field A	MATCHES	Field B	Field C	Field D	Field E	Field F
2/1/2020	VALUE	RECORD create DATE happened before FIELD A create date (no match)	VALUE	VALUE
3/2/2020	VALUE	RECORD create DATE happened on or after FIELD MATCH	VALUE	VALUE
9/3/2019		RECORD create DATE happened before FIELD A create date (no match)		VALUE
3/4/2020	VALUE	RECORD create DATE happened on or after FIELD MATCH

Assistance in the haulting of my hither and thither would make me, this day and even (dare i presume) Mr. Collins most agreeable!

Ted

 

[tnesseth]

Nope, I've read it twice, and it doesn't make sense. I think you ought to have another go at explaining what your table contents are (i.e. meaning), and what you need to do ... maybe explaining the logic in English.

Glenn!! THANK YOU FOR THE FEED BACK!!! much appreciated and apologies for the lack of clarity. although I am pretty sure its English (albeit not the Kings ...i will give you that good sir.) So utilization of fields report factoring in the date the field was created against the date a record was created.. if the field is blank it may be because it want there Example: if I run a rpt for field usage on a table w 1000 records and declare "no one is using this field, there is only one record where it is not blank. we need to DELETE IT) . and someone says to me: "I created that field this morning" I want to avoid that convo

Two tables

Table one: Field and the date it was created for use in the table (meta)

FIELD from table	FIELD created DATE	EXPECTED RESULTS - column 3 below has formula to return
field A	2/25/2020	2 - from example below
field B	6/2/2016
field B	1/1/2000
field D	8/1/2011
field E	8/1/2011

Table two: All the records created by users and all fields

Record create date	Field A	MATCHES	Field B	Field C	Field D	Field E	Field F
2/1/2020	VALUE	RECORD create DATE happened before FIELD A create date (no match)	VALUE	VALUE
3/2/2020	VALUE	RECORD create DATE happened on or after FIELD MATCH	VALUE	VALUE
9/3/2019		RECORD create DATE happened before FIELD A create date (no match)		VALUE
3/4/2020	VALUE	RECORD create DATE happened on or after FIELD MATCH

Assistance in the haulting of my hither and thither would make me, this day and even (dare i presume) Mr. Collins most agreeable!

Ted

 

[GlennUK]

OK, I think I'm starting to understand. So, don't you just need to fetch the Field creation date for a formula in the bottom table and do a simple compare to the record create date (compare using greater than sign)?

 

[tnesseth]

OK, I think I'm starting to understand. So, don't you just need to fetch the Field creation date for a formula in the bottom table and do a simple compare to the record create date (compare using greater than sign)?

that yes and then sum the number of matches in each table 2 column to produce results for table 1
if field create is before record create AND there is a value in the field
I have a nested if nut i need it to sum the whole column - ignore the 'T","F" if should be a sum of all in column that meet criteria
hope this is helpful -and thanks again!

 

[GlennUK]

The IF goes in Table 2. Why are you putting it in Table 1?

 

[tnesseth]

The IF goes in Table 2. Why are you putting it in Table 1?

Table 1 in the ONE
and
Table 2 (field names from table 1 transposed as header), is the MANY

I cheated it to make it easier for the date condition
i did a vlookup on the from table two to get the vertical date from table one in Row 2

 

[GlennUK]

I know the relationship ... I said to do the test in the MANY and collate in the ONE ... which is exactly what you've done ... except you did a countifs instead of testing each row, which is great. So, I'd say your problem is solved?

 

[tnesseth]

I know the relationship ... I said to do the test in the MANY and collate in the ONE ... which is exactly what you've done ... except you did a countifs instead of testing each row, which is great. So, I'd say your problem is solved?

Yes sir. thanks. i still would love to know how to in one move