Need help with a formula (countif)

%3CLINGO-SUB%20id%3D%22lingo-sub-1326439%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20a%20formula%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1326439%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20using%20the%20follow%20formula%3A%20%3DAANTAL.ALS(P60%3AP65%3B%20L62)%20I%20think%26nbsp%3B%3DAANTAL.ALS%20is%20in%20English%20%3Dcountif%3C%2FP%3E%3CP%3EI%20want%20the%20add%20more%20rows%26nbsp%3Bin%20the%20same%20sheet%20with%20only%201%20goal%20(B4)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20been%20bust%20to%20work%20this%20out%2C%20but%20it%20is%20not%20working%20with%20four%20formula%2C%20after%203%20times%20I%20will%20not%20count%20up.%3C%2FP%3E%3CP%3ESee%20below%3A%3C%2FP%3E%3CP%3E%3DAANTAL.ALS(P60%3AP65%3BL62)%2BAANTAL.ALS(P128%3AP153%3BL62)%2BAANTAL.ALS(P162%3AP171%3BL62)%2BAANTAL.ALS(P198%3AP226%3BL62)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anybody%20help%20me%2C%20if%20been%20working%20on%20the%20last%20few%20days%20with%20this.%20It%20need%20to%20select%20different%20rows%20with%201%20goals%20and%20not%20more%20goals.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1326439%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1327798%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20formula%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1327798%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F631527%22%20target%3D%22_blank%22%3E%40johanchristiaan%3C%2FA%3E%26nbsp%3BPulled%20your%20Dutch%20formula%20through%20a%20translator%20to%20English%20and%20inserted%20it%20into%20a%20clean%20sheet%2C%20entering%20%22a%22%20in%20column%20P%20(far%20enough%20down%20to%20test%20your%20formula)%20and%20%22a%22%20in%20L62.%20I%20count%2071%20occurrences%2C%20as%20expected%20from%20the%20ranges%20included%20in%20the%20formula.%26nbsp%3BWhat%20are%20%3CSTRONG%3Eyou%3C%2FSTRONG%3E%20trying%20to%20count%3F%20Are%20you%20sure%20that%20whatever%20is%20entered%20in%20the%204th%20range%20actually%20contains%20an%20exact%20match%20with%20what%20is%20in%20L62%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1327853%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20formula%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1327853%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20the%20formula%20needs%20to%20look%20at%20P60%3AP65%2C%20P128%3AP53%2C%20P162%3A171%20and%20the%20last%20one%20P198%3AP226%3C%2FP%3E%3CP%3EFor%20something%20that's%20is%20L62.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20I%20set%20up%20the%20formula%20for%20only%202%20areas%26nbsp%3BP60%3AP65%2C%20P128%3AP53%20and%26nbsp%3BP162%3A171%20it%20will%20work.%20But%20if%20I%20add%20the%26nbsp%3BP198%3AP226%20the%20formula%20will%20not%20work%20any%20more.%20I%20will%20have%20only%20the%20formula%20in%20the%20cel%20and%20not%20the%20out%20coming.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20it%20be%20that%20the%20formula%20is%20to%20long%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1327907%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20formula%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1327907%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F631527%22%20target%3D%22_blank%22%3E%40johanchristiaan%3C%2FA%3E%26nbsp%3BHave%20a%20look%20at%20the%20attached%20workbook.%20I%20created%20it%20in%20an%20English%20version%2C%20but%20it%20will%20translate%20to%20Dutch%20when%20you%20open%20it.%20The%20formula%20(in%20A1)%20works%20as%20intended.%20You%20didn't%20answer%20WHAT%20you%20are%20trying%20to%20count.%20Texts%2C%20numbers%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1328081%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20formula%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1328081%22%20slang%3D%22en-US%22%3E%3CP%3EFANTASTIC!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1328133%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20formula%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1328133%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F631527%22%20target%3D%22_blank%22%3E%40johanchristiaan%3C%2FA%3E%26nbsp%3BGraag%20gedaan!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi

 

I using the follow formula: =AANTAL.ALS(P60:P65; L62) I think =AANTAL.ALS is in English =countif

I want the add more rows in the same sheet with only 1 goal (B4)

 

I been bust to work this out, but it is not working with four formula, after 3 times I will not count up.

See below:

=AANTAL.ALS(P60:P65;L62)+AANTAL.ALS(P128:P153;L62)+AANTAL.ALS(P162:P171;L62)+AANTAL.ALS(P198:P226;L62)

 

Can anybody help me, if been working on the last few days with this. It need to select different rows with 1 goals and not more goals.

 

Thanks!

 

 

5 Replies
Highlighted

@johanchristiaan Pulled your Dutch formula through a translator to English and inserted it into a clean sheet, entering "a" in column P (far enough down to test your formula) and "a" in L62. I count 71 occurrences, as expected from the ranges included in the formula. What are you trying to count? Are you sure that whatever is entered in the 4th range actually contains an exact match with what is in L62? 

Highlighted

@Riny_van_Eekelen 

 

Basically the formula needs to look at P60:P65, P128:P53, P162:171 and the last one P198:P226

For something that's is L62.

 

if I set up the formula for only 2 areas P60:P65, P128:P53 and P162:171 it will work. But if I add the P198:P226 the formula will not work any more. I will have only the formula in the cel and not the out coming. 

 

Can it be that the formula is to long?

 

Highlighted

@johanchristiaan Have a look at the attached workbook. I created it in an English version, but it will translate to Dutch when you open it. The formula (in A1) works as intended. You didn't answer WHAT you are trying to count. Texts, numbers?

 

Highlighted

FANTASTIC! 

 

Thanks

Highlighted

@johanchristiaan Graag gedaan!