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
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

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

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies