Summing Dates In A Single Column If Values In Another Column Equal Specific Cell Data

%3CLINGO-SUB%20id%3D%22lingo-sub-1427174%22%20slang%3D%22en-US%22%3ESumming%20Dates%20In%20A%20Single%20Column%20If%20Values%20In%20Another%20Column%20Equal%20Specific%20Cell%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427174%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20simple%26nbsp%3Binspection%20report%20that%20I%20track%20annually.%20I%20have%20recently%20had%20to%20start%20subdividing%20the%20cumulative%20data%20into%2042%20cells%20for%20each%20inspection%20type.%20There%20are%203%20inspection%20types%20so%20126%20cells%20total.%20I%20am%20looking%20for%20a%20fast%20way%20to%20do%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20constants%20are%20(1)%20data%20in%20Column%20E%20equals%20a%20certain%20area%20name%20(ex%3A%20Miscellaneous%2C%20Other%2C%20etc)%20and%20(2)%20values%20in%20Cells%20C1483-C1524%20equal%20a%20certain%20area%20name%20that%26nbsp%3Bcorrespond%20to%20the%26nbsp%3Bvalues%20in%20Column%20E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20variables%20are%20the%20dates%20the%20inspection%20reports%20are%20done%20in%20Column's%20H%2C%20J%20and%20N.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20write%20a%20formula%20that%20will%20sum%20the%26nbsp%3Bdates%20in%20Column%20H%20if%20the%20area%20name%20in%20Column%20E%20matches%20the%20cell%20name%20for%20each%20of%20the%26nbsp%3B42%20areas.%20So%2C%20for%20example%2C%20if%20column%20H%20has%20a%20date%20anywhere%20from%201.1.2020%20to%2012.31.2020%2C%20and%20the%20area%20I'm%20working%20on%26nbsp%3Bfrom%26nbsp%3BCells%20C1483-C1524%20is%26nbsp%3B%22Miscellaneous%2C%22%26nbsp%3BI%20would%20like%20Excel%20to%20look%20at%20Column%20E%20and%20find%20all%20%22Miscellaneous%22%20rows%20with%20a%20date%20in%20that%20range%20and%20give%20me%20a%20sum.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20SUMIF%2C%20COUNTIF%2C%20COUNTA%2C%20and%20a%20couple%20of%20others%2C%20but%20getting%20the%20date%20to%20be%20recognized%20as%20%221%22%20if%20Column%20E%20matches%20a%20certain%20value%20has%20not%20yet%20been%20attained.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20has%20any%20suggestions%20on%20how%20to%20make%20this%20work%2C%20I%20would%20be%20very%20appreciative.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1427174%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-1427305%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20Dates%20In%20A%20Single%20Column%20If%20Values%20In%20Another%20Column%20Equal%20Specific%20Cell%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427305%22%20slang%3D%22en-US%22%3EHello%2C%20can%20you%20upload%20a%20sample%20file%20for%20better%20understanding%3F%3CBR%20%2F%3E%3CBR%20%2F%3ESUMPRODUCT%20should%20be%20able%20to%20handle%20this%20kind%20of%20problem%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1427453%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20Dates%20In%20A%20Single%20Column%20If%20Values%20In%20Another%20Column%20Equal%20Specific%20Cell%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427453%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%20I%20have%20attached%20a%20generic%20example.%20I%20appreciate%20the%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,

 

I have a simple inspection report that I track annually. I have recently had to start subdividing the cumulative data into 42 cells for each inspection type. There are 3 inspection types so 126 cells total. I am looking for a fast way to do this.

 

The constants are (1) data in Column E equals a certain area name (ex: Miscellaneous, Other, etc) and (2) values in Cells C1483-C1524 equal a certain area name that correspond to the values in Column E.

 

The variables are the dates the inspection reports are done in Column's H, J and N.

 

I am trying to write a formula that will sum the dates in Column H if the area name in Column E matches the cell name for each of the 42 areas. So, for example, if column H has a date anywhere from 1.1.2020 to 12.31.2020, and the area I'm working on from Cells C1483-C1524 is "Miscellaneous," I would like Excel to look at Column E and find all "Miscellaneous" rows with a date in that range and give me a sum.

 

I have tried SUMIF, COUNTIF, COUNTA, and a couple of others, but getting the date to be recognized as "1" if Column E matches a certain value has not yet been attained.

 

If anyone has any suggestions on how to make this work, I would be very appreciative.

 

Thanks in advance!

2 Replies
Highlighted
Hello, can you upload a sample file for better understanding?

SUMPRODUCT should be able to handle this kind of problem
Highlighted

@Abiola1 

 

Hello,

 

Thank you for your reply. I have attached a generic example. I appreciate the help!