Home

Excel formula Help Needed SUMPRODUCT

%3CLINGO-SUB%20id%3D%22lingo-sub-766358%22%20slang%3D%22en-US%22%3EExcel%20formula%20Help%20Needed%20SUMPRODUCT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766358%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20regarding%20excel%20formula.%20I%20want%20to%20make%20a%20excel%20chart%20where%20if%20there%20is%20only%20numeric%20number%20in%20the%20cell%20that%20can%20count%20but%20if%20the%20cell%20is%20blank%20or%20text%20that%20can%20not%20count%20.%20Help%20please%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-766358%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766367%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%20Needed%20SUMPRODUCT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766367%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379698%22%20target%3D%22_blank%22%3E%40Nazmulhasan911%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20explain%20your%20data%20and%20expected%20result%20through%20a%20sample%20or%20actual%20file.%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766384%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%20Needed%20SUMPRODUCT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766384%22%20slang%3D%22en-US%22%3EIf%20the%20cells%20you%20want%20to%20evaluate%20are%20in%20Column%20A%20and%20you%20want%20to%20count%20the%20number%20of%20cells%20that%20include%20a%20number%20in%20them%2C%20then%20this%20formula%20may%20return%20the%20count%20you%20want%3A%3CBR%20%2F%3E%3DSUMPRODUCT(%E2%80%94ISNUMBER(FIND(ROW(1%3A10)-1%2C%3CBR%20%2F%3EA%3AA)))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-767247%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help%20Needed%20SUMPRODUCT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-767247%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379698%22%20target%3D%22_blank%22%3E%40Nazmulhasan911%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20didn't%20test%20my%20earlier%20formula.%20Upon%20testing%2C%20such%20formula%20sadly%20returned%20an%20incorrect%20result.%20Conversely%2C%20this%20formula%20returns%20the%20correct%20result%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH(%22*%22%26amp%3BCOLUMN(A1%3AJ1)-1%26amp%3B%22*%22%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EA%3AA))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EROW(1%3A10)%5E0)%26gt%3B0))%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Nazmulhasan911
Occasional Visitor

Hi ,

 

I need help regarding excel formula. I want to make a excel chart where if there is only numeric number in the cell that can count but if the cell is blank or text that can not count . Help please 

3 Replies

@Nazmulhasan911 

 

Please explain your data and expected result through a sample or actual file.

Thanks,

If the cells you want to evaluate are in Column A and you want to count the number of cells that include a number in them, then this formula may return the count you want:
=SUMPRODUCT(—ISNUMBER(FIND(ROW(1:10)-1,
A:A)))

@Nazmulhasan911 

I didn't test my earlier formula. Upon testing, such formula sadly returned an incorrect result. Conversely, this formula returns the correct result: 

=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH("*"&COLUMN(A1:J1)-1&"*",
A:A)),
ROW(1:10)^0)>0))

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
17 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies