SOLVED

Formulas and Functions

%3CLINGO-SUB%20id%3D%22lingo-sub-2239792%22%20slang%3D%22en-US%22%3EFormulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2239792%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20can%20someone%20please%20help%20me%20with%20the%20proper%20formula%20for%20the%20below%20scenario%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20a%20Tick%20symbol%20as%20the%20value.%20I%20am%20tracking%20student%20attendance.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20know%20the%20total%20Sum%20for%20each%20row.%20Each%20row%20represents%20a%20student.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20there%20are%2031%20Tick%20symbols%20on%20row%209%20(columns%20A%20thru%20Y).%3C%2FP%3E%3CP%3EEach%20Tick%20symbol%20represents%20%245.00%20per%20day.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20multiple%20formulas.%20The%20below%20is%20the%20latest%20formula%20I%20tried.%3C%2FP%3E%3CP%3E%3DSUM(LEN(A9%3AY9*5))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2239792%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2239798%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2239798%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1010435%22%20target%3D%22_blank%22%3E%40Gmema2u%3C%2FA%3E%26nbsp%3BTry%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(LEN(A9%3AY9)*5)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20though%20why%20you%20chose%20to%20use%20tick%20marks.%20Why%20not%20just%20a%20number%201.%20So%20much%20easier%20to%20calculate%20with%20numbers.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, can someone please help me with the proper formula for the below scenario?

 

I used a Tick symbol as the value. I am tracking student attendance.

I would like to know the total Sum for each row. Each row represents a student.

 

For example, there are 31 Tick symbols on row 9 (columns A thru Y).

Each Tick symbol represents $5.00 per day. 

I have tried multiple formulas. The below is the latest formula I tried.

=SUM(LEN(A9:Y9*5))

5 Replies

@Gmema2u Try this:

 

=SUM(LEN(A9:Y9)*5)

 

Not sure though why you chose to use tick marks. Why not just a number 1. So much easier to calculate with numbers. 

best response confirmed by Gmema2u (New Contributor)
Solution

@Gmema2u 

 

Use

 

=SUMPRODUCT(LEN(A9:Y9))*5

 

Note that the multiplication *5 is outside the parentheses.

@Gmema2u 

 

Hi

 

Attached is a possible solution - you count the blanks (using the COUNTBLANK function) rather than the tick symbols and then use the number for the calculation.

 

hope this helps.

 

Peter

It worked! Yeah!!! Thank you so very much!
Hi @peteryac60, another Member shared the below formula with me which worked. Yeah. Thanks for your reply.

Here is the formula: =SUMPRODUCT(LEN(A9:Y9))*5