SOLVED
Home

Countif date in Column A is greater than date in Column B

%3CLINGO-SUB%20id%3D%22lingo-sub-461655%22%20slang%3D%22en-US%22%3ECountif%20date%20in%20Column%20A%20is%20greater%20than%20date%20in%20Column%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-461655%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20attached%20a%20sample%20xlsx%20file%20containing%202%20sheets%2C%20%22Data%22%20and%20%22Results%22.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20%22Data%22%20sheet%20contains%20columns%20for%20the%20date%20something%20was%20scheduled%2C%20%22Scheduled%20Date%22%2C%20and%20the%20date%20it%20was%20actually%20completed%20%22Completed%20Date%22.%26nbsp%3B%20It%20also%20contains%20a%20column%20for%20the%20type%20of%20work%2C%20%22Type%22.%3CBR%20%2F%3E%3CBR%20%2F%3EI'd%20like%20to%20count%20-%20via%20a%20formula%20(and%20not%20VBA)%20-%20how%20many%20items%20were%20completed%20late%2C%20for%20each%20type%20of%20work.%26nbsp%3B%20I've%20tried%20using%20COUNTIF%20and%20SUMPRODUCT%20but%20have%20not%20been%20able%20to%20figure%20it%20out%2C%20and%20would%20appreciate%20any%20help.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20%22Results%22%20sheet%20has%20sample%20results%20showing%20that%20for%20the%20%22Required%22%20type%20of%20work%20there%20are%203%20late%20items%2C%20and%20for%20the%20%22Suggested%22%20type%20of%20work%20there's%201%20late%20item.%3CBR%20%2F%3E%3CBR%20%2F%3EEDIT%3A%20When%20working%20with%20COUNTIFS%20my%20problem%20was%20that%20I%20couldn't%2C%20or%20didn't%20know%20how%2C%20to%20reference%20a%20different%20row%20so%20that%20I%20could%20say%20%22If%20the%20date%20in%20this%20column%20is%20greater%20than%20the%20date%20in%20that%20column...%22%3CBR%20%2F%3E%3CBR%20%2F%3EI%20tried%20a%20convoluted%20method%20of%20getting%20the%20offset%20of%20the%20cell%20with%20INDIRECT%20and%20ADDRESS%20and%20so%20on%2C%20but%20while%20that%20worked%20in%20a%20test%2C%20it%20did%20not%20work%20within%20the%20context%20of%20a%20COUNTIF.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-461655%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-461855%22%20slang%3D%22en-US%22%3ERe%3A%20Countif%20date%20in%20Column%20A%20is%20greater%20than%20date%20in%20Column%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-461855%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322013%22%20target%3D%22_blank%22%3E%40matthew_frank%3C%2FA%3E%26nbsp%3B%2C%20it%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((Data!%24B%242%3A%24B%249%26gt%3BData!%24D%242%3A%24D%249)*(A2%3DData!%24A%242%3A%24A%249))%3C%2FPRE%3E%0A%3CP%3E(adjust%20to%20actual%20range%2C%20or%20use%20dynamic%20ranges%20%2F%20tables)%20if%20only%20you%20don't%20keep%20Completed%20as%20text%20(zero%20or%20null%20cell).%20Text%20is%20always%20more%20than%20any%20number%2Fdate.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-462126%22%20slang%3D%22en-US%22%3ERe%3A%20Countif%20date%20in%20Column%20A%20is%20greater%20than%20date%20in%20Column%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-462126%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20-%20that%20works%20and%20gives%20me%20something%20to%20play%20with.%3CBR%20%2F%3E%3CBR%20%2F%3E-%20Unfortunately%20I%20can't%20remove%20the%20%220%22%20string%20as%20it's%20an%20artifact%20of%20how%20the%20data%20is%20produced%2C%20but%20I%20was%20able%20to%20handle%20it%20with%20%3CSTRONG%3E*(%220%22%26lt%3B%26gt%3BData!%24B%242%3A%24B%249))%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E-%20Re%3A%20Using%20a%20Dynamic%20Range%3A%20would%20that%20be%20something%20like%20using%20COUNTA%20determine%20how%20many%20rows%20are%2C%20so%20that%20any%20change%20in%20length%20is%20reflected%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20tried%20using%20B%3AB%20to%20simply%20say%20%22Look%20at%20everything%22%2C%20but%20it%20failed.%26nbsp%3B%20A%20simple-stupid%20alternative%20which%20worked%20was%20to%20simply%20say%20B2%3AB1000%2C%20since%20I'll%20never%20have%20anywhere%20near%20that%2C%20and%20it's%20still%20fast.%26nbsp%3B%20But%20it%20would%20be%20nice%20to%20have%20it%20fully%20dynamic.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-462334%22%20slang%3D%22en-US%22%3ERe%3A%20Countif%20date%20in%20Column%20A%20is%20greater%20than%20date%20in%20Column%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-462334%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322013%22%20target%3D%22_blank%22%3E%40matthew_frank%3C%2FA%3E%26nbsp%3B%2C%20you%20may%20use%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((Data!%24B%242%3AINDEX(Data!%24B%3A%24B%2CCOUNTA(Data!%24B%3A%24B))%26gt%3BData!%24D%242%3AINDEX(Data!%24D%3A%24D%2CCOUNTA(Data!%24D%3A%24D)))*(A2%3DData!%24A%242%3AINDEX(Data!%24A%3A%24A%2CCOUNTA(Data!%24A%3A%24A)))*((Data!%24B%242%3AINDEX(Data!%24B%3A%24B%2CCOUNTA(Data!%24B%3A%24B))%26lt%3B%26gt%3B%220%22)))%3C%2FPRE%3E%0A%3CP%3EPlease%20see%20in%20attached%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-462377%22%20slang%3D%22en-US%22%3ERe%3A%20Countif%20date%20in%20Column%20A%20is%20greater%20than%20date%20in%20Column%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-462377%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThank%20you%20-%20that%20helped%20immensely.%3C%2FP%3E%3C%2FLINGO-BODY%3E
matthew_frank
New Contributor

I've attached a sample xlsx file containing 2 sheets, "Data" and "Results". 

The "Data" sheet contains columns for the date something was scheduled, "Scheduled Date", and the date it was actually completed "Completed Date".  It also contains a column for the type of work, "Type".

I'd like to count - via a formula (and not VBA) - how many items were completed late, for each type of work.  I've tried using COUNTIF and SUMPRODUCT but have not been able to figure it out, and would appreciate any help.

The "Results" sheet has sample results showing that for the "Required" type of work there are 3 late items, and for the "Suggested" type of work there's 1 late item.

EDIT: When working with COUNTIFS my problem was that I couldn't, or didn't know how, to reference a different row so that I could say "If the date in this column is greater than the date in that column..."

I tried a convoluted method of getting the offset of the cell with INDIRECT and ADDRESS and so on, but while that worked in a test, it did not work within the context of a COUNTIF.

4 Replies
Solution

@matthew_frank , it could be

=SUMPRODUCT((Data!$B$2:$B$9>Data!$D$2:$D$9)*(A2=Data!$A$2:$A$9))

(adjust to actual range, or use dynamic ranges / tables) if only you don't keep Completed as text (zero or null cell). Text is always more than any number/date.

 

Thank you - that works and gives me something to play with.

- Unfortunately I can't remove the "0" string as it's an artifact of how the data is produced, but I was able to handle it with *("0"<>Data!$B$2:$B$9))

- Re: Using a Dynamic Range: would that be something like using COUNTA determine how many rows are, so that any change in length is reflected?

I tried using B:B to simply say "Look at everything", but it failed.  A simple-stupid alternative which worked was to simply say B2:B1000, since I'll never have anywhere near that, and it's still fast.  But it would be nice to have it fully dynamic.

@matthew_frank , you may use

=SUMPRODUCT((Data!$B$2:INDEX(Data!$B:$B,COUNTA(Data!$B:$B))>Data!$D$2:INDEX(Data!$D:$D,COUNTA(Data!$D:$D)))*(A2=Data!$A$2:INDEX(Data!$A:$A,COUNTA(Data!$A:$A)))*((Data!$B$2:INDEX(Data!$B:$B,COUNTA(Data!$B:$B))<>"0")))

Please see in attached

 

@Sergei BaklanThank you - that helped immensely.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies