SOLVED
Home

Counting multiple cells problem (countif)

%3CLINGO-SUB%20id%3D%22lingo-sub-391443%22%20slang%3D%22en-US%22%3ECounting%20multiple%20cells%20problem%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391443%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHaving%20trouble%20getting%20a%20formula%20to%20count%20an%20entire%20cell%20range%20for%20me.%20%26nbsp%3BI'm%20assigning%20hockey%20games%20and%20trying%20to%20figure%20out%20how%20many%20times%20one%20referee%20will%20see%20a%20team.%20%26nbsp%3BHow%20do%20I%20count%20row%20by%20row%20if%20an%20official%20is%20to%20see%20the%20team.%20%26nbsp%3B(and%20it%20can't%20be%20a%20full%20range%20on%20the%20page%2C%20or%20it%20tells%20me%20how%20many%20times%20in%20the%20cells%20I%20have%20the%20referee%20and%20then%20the%20team%2C%20not%20taking%20into%20account%20that%20they%20maybe%20on%20different%20games.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20the%20countifs%20function%20and%20can't%20seem%20to%20get%20it%20to%20work....I'm%20pretty%20good%20with%20excel%20so%20pulling%20out%20my%20hair%20here%20%3A-)%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EPhil%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-391443%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-392116%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20multiple%20cells%20problem%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-392116%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F311333%22%20target%3D%22_blank%22%3E%40GrasshoppersPhil%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20formula%20evaluation.%3C%2FP%3E%3CP%3EThe%20matches%20result%20in%20a%20two-dimensional%20matrix%20with%20TRUE%20and%20FALSE.%3C%2FP%3E%3CP%3E--%20converts%20them%20to%201%20and%200.%3C%2FP%3E%3CP%3EMMULT()%20transform%20them%20into%20a%20one-dimensional%20column.%3C%2FP%3E%3CP%3ESUMPRODUCT()%20multiplies%20both%20matrices%20and%20adds%20them%20up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391588%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20multiple%20cells%20problem%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391588%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F311333%22%20target%3D%22_blank%22%3E%40GrasshoppersPhil%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20some%20time%2C%20can%20you%20explain%20the%20formula%20to%20me%3F%20%26nbsp%3BI%20understand%20some%20of%20it%20%3A-)%20thanks%3C%2FP%3E%3CP%3EPhil%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391517%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20multiple%20cells%20problem%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391517%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F311333%22%20target%3D%22_blank%22%3E%40GrasshoppersPhil%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20a%20few%20things%20and%20this%20worked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391514%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20multiple%20cells%20problem%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391514%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20response...%20this%20is%20awesome...how%20did%20you%20get%20this%20formula%20(if%20you%20don't%20mind%20me%20asking%20as%20I%20use%20excel%20often%20and%20am%20now%20very%20curious).%20%26nbsp%3B%3CBR%20%2F%3EThanks%3C%2FP%3E%3CP%3EPhil%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391476%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20multiple%20cells%20problem%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391476%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F311333%22%20target%3D%22_blank%22%3E%40GrasshoppersPhil%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DSUMPRODUCT(MMULT(--(%24E%242%3A%24H%2425%3D%24K3)%2C%7B1%3B1%3B1%3B1%7D)*MMULT(--(%24B%242%3A%24C%2425%3DN%241)%2C%7B1%3B1%7D))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391454%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20multiple%20cells%20problem%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391454%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BThat's%20your%20only%20advice%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391450%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20multiple%20cells%20problem%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391450%22%20slang%3D%22en-US%22%3EI%20suggest%20that%20you%20first%20convert%20the%20layout%20of%20your%20data%20to%20flat%20file%20format%20to%20facilitate%20calculations%20thereon.%20Unmerge%20all%20cells.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391447%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20multiple%20cells%20problem%20(countif)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391447%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F311333%22%20target%3D%22_blank%22%3E%40GrasshoppersPhil%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20here%20is%20the%20excel%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
GrasshoppersPhil
Occasional Contributor

Hello all,

 

Having trouble getting a formula to count an entire cell range for me.  I'm assigning hockey games and trying to figure out how many times one referee will see a team.  How do I count row by row if an official is to see the team.  (and it can't be a full range on the page, or it tells me how many times in the cells I have the referee and then the team, not taking into account that they maybe on different games.

 

I tried the countifs function and can't seem to get it to work....I'm pretty good with excel so pulling out my hair here :-)

Thanks

Phil

8 Replies
I suggest that you first convert the layout of your data to flat file format to facilitate calculations thereon. Unmerge all cells.

@Twifoo That's your only advice?

@GrasshoppersPhil 

 

=SUMPRODUCT(MMULT(--($E$2:$H$25=$K3),{1;1;1;1})*MMULT(--($B$2:$C$25=N$1),{1;1}))

 

Solution

@Detlef Lewin 

Thanks for the response... this is awesome...how did you get this formula (if you don't mind me asking as I use excel often and am now very curious).  
Thanks

Phil

@GrasshoppersPhil 

I tried a few things and this worked.

 

@GrasshoppersPhil 

 

If you have some time, can you explain the formula to me?  I understand some of it :-) thanks

Phil

@GrasshoppersPhil 

Use formula evaluation.

The matches result in a two-dimensional matrix with TRUE and FALSE.

-- converts them to 1 and 0.

MMULT() transform them into a one-dimensional column.

SUMPRODUCT() multiplies both matrices and adds them up.