SOLVED
Home

Comparing dates in categories.

%3CLINGO-SUB%20id%3D%22lingo-sub-835414%22%20slang%3D%22en-US%22%3EComparing%20dates%20in%20categories.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-835414%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20only%20use%20a%20formula%20and%20not%20VBA.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%203%20different%20columns%20I%20need%20to%20use%20for%20my%20formula%3A%20A%20category%2C%20a%20due%20date%2C%20and%20an%20actual%20date.%20I%20need%20to%20be%20able%20to%20calculate%20how%20many%20entries%20for%20each%20category%20have%20an%20actual%20date%20that%20is%20later%20than%20the%20due%20date.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20formula%26nbsp%3B%3DSUMPRODUCT((C2%3AC13%26gt%3BB2%3AB13)*1)%2C%20I%20am%20able%20to%20determine%20the%20count%20for%20all%20of%20the%20parts%20that%20have%20an%20actual%20date%20past%20the%20due%20date.%20However%2C%20I%20am%20unable%20to%20find%20a%20formula%20that%20works%20with%20separating%20this%20information%20into%20each%20category.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20attached%20example%2C%20I%20see%20that%20there%20are%204%20entries%20that%20are%20late%20using%20my%20above%20formula.%201%20is%20in%20category%20A%2C%202%20are%20in%20category%20B%2C%20and%201%20is%20in%20category%20C.%20However%2C%20I%20do%20not%20have%20a%20formula%20that%20tells%20me%20this%20information%20for%20each%20category.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%20with%20this%3F%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-835414%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-835621%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20dates%20in%20categories.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-835621%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402827%22%20target%3D%22_blank%22%3E%40Justine714%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Etry%20in%20cell%20A17%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT((%24C%242%3A%24C%2413%26gt%3B%24B%242%3A%24B%2413)*(%24A%242%3A%24A%2413%3DA%2416))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ecopy%20to%20the%20right.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20this%20post%20solves%20your%20problem%2C%20please%20mark%20it%20as%20%22Best%20Response%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-838835%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20dates%20in%20categories.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-838835%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much!%20That%20worked!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Justine714
New Contributor

Hello,

 

I am trying to only use a formula and not VBA. 

 

I have 3 different columns I need to use for my formula: A category, a due date, and an actual date. I need to be able to calculate how many entries for each category have an actual date that is later than the due date. 

 

With the formula =SUMPRODUCT((C2:C13>B2:B13)*1), I am able to determine the count for all of the parts that have an actual date past the due date. However, I am unable to find a formula that works with separating this information into each category. 

 

In my attached example, I see that there are 4 entries that are late using my above formula. 1 is in category A, 2 are in category B, and 1 is in category C. However, I do not have a formula that tells me this information for each category.

 

Can anyone help me with this? Thank you. 

2 Replies
Solution

Hello @Justine714,

 

try in cell A17

 

=SUMPRODUCT(($C$2:$C$13>$B$2:$B$13)*($A$2:$A$13=A$16))

 

copy to the right.

 

If this post solves your problem, please mark it as "Best Response".

@Ingeborg Hawighorst Thank you so much! That worked!

Related Conversations
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
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies