Home

Totaling using two sets of data

%3CLINGO-SUB%20id%3D%22lingo-sub-848856%22%20slang%3D%22en-US%22%3ETotaling%20using%20two%20sets%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-848856%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20count%20and%20get%20the%20total%20of%202%20sets%20of%20data%3A%20one%20set%20includes%20dates%2C%20the%20other%20includes%20words.%20The%20dates%20all%20include%20month%2C%20day%2C%20and%20years%2C%20but%20the%20data%20I%20would%20like%20to%20use%20in%20my%20results%20would%20just%20consist%20of%20the%20years.%20I'm%20working%20with%20semi%20trailer%20repairs%2C%20so%20the%20formula%20I%20would%20like%20to%20use%20should%20be%20saying%20%22count%20all%20issues%20only%20if%20year%22...like%20%22count%20all%20tire%20repairs%20only%20if%202016%22.%20I%20have%20been%20googling%20and%20looking%20around%2C%20but%20all%20I%20can%20find%20on%20the%20subject%20is%20to%20get%20a%20result%20of%20true%20or%20false%2C%20but%20I%20want%20a%20number%20as%20my%20result.%20so%20far%2C%20I%20have%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3DCOUNTIF(issues%2C%22axles%22)%2BCOUNTIFS(dates%2C%22%26gt%3B%3D%22%26amp%3BDATE(2016%2C1%2C1)%2Cdates%2C%22%26lt%3B%3D%22%26amp%3BDATE(2016%2C12%2C31)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20named%20one%20column%20%22issues%22%20and%20another%20column%20%22dates.%22%20(%22axles%22%20is%20one%20of%20the%20issues%20I'm%20trying%20to%20total.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20this%20formula%20is%20valid%2C%20but%20it%20adds%20the%20totals%20of%20the%20%22issues%22%20column%20and%20the%20%22dates%22%20column%26nbsp%3B%20separately%20instead%20of%20taking%20both%20into%20account%20in%20the%20total%2C%20if%20that%20makes%20sense.%20Hopefully%20someone%20can%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-848856%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECOUNTIF%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECOUNTIFS%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Edate%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETotaling%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-848917%22%20slang%3D%22en-US%22%3ERe%3A%20Totaling%20using%20two%20sets%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-848917%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F407262%22%20target%3D%22_blank%22%3E%40dazedandconfused%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20adding%20result%20of%20two%20countifs%20that%20is%20the%20reason%20you%20do%20not%20get%20the%20result.%3C%2FP%3E%3CP%3EYou%20can%20use%20below%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCOUNTIFS(%24D%242%3A%24D%2410%2C%22axles%22%2C%24E%242%3A%24E%2410%2C%22%26gt%3B%3D%22%26amp%3BDATE(2016%2C1%2C1)%2C%24E%242%3A%24E%2410%2C%22%26lt%3B%3D%22%26amp%3BDATE(2016%2C12%2C31))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20file%20is%20also%20attached%20for%20your%20reference.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-848919%22%20slang%3D%22en-US%22%3ERe%3A%20Totaling%20using%20two%20sets%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-848919%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
dazedandconfused
Occasional Visitor

I am trying to count and get the total of 2 sets of data: one set includes dates, the other includes words. The dates all include month, day, and years, but the data I would like to use in my results would just consist of the years. I'm working with semi trailer repairs, so the formula I would like to use should be saying "count all issues only if year"...like "count all tire repairs only if 2016". I have been googling and looking around, but all I can find on the subject is to get a result of true or false, but I want a number as my result. so far, I have this:

 

=COUNTIF(issues,"axles")+COUNTIFS(dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,12,31)

 

I named one column "issues" and another column "dates." ("axles" is one of the issues I'm trying to total.)

 

I know this formula is valid, but it adds the totals of the "issues" column and the "dates" column  separately instead of taking both into account in the total, if that makes sense. Hopefully someone can help!

2 Replies
Highlighted

Hi @dazedandconfused 

 

You are adding result of two countifs that is the reason you do not get the result.

You can use below formula: 

 

=COUNTIFS($D$2:$D$10,"axles",$E$2:$E$10,">="&DATE(2016,1,1),$E$2:$E$10,"<="&DATE(2016,12,31))

 

Sample file is also attached for your reference.

Thanks

Tauqeer

Highlighted
Related Conversations
History and Data
rosenbloomsnka in Microsoft To Do on
0 Replies
How often the sync is triggered?
HotCakeX in Discussions on
5 Replies
Pulling data from one tab to another
krysphares in Excel on
6 Replies