Sum Text Fields

[Brett Hagen]

Is there a way to sum by both column and row cells which contain text in the
format #.##T. I am looking to sum the numbers that end in 'P' and the
numbers that end in 'V'.


2.33P 1P 3P
3P
.5V 2V .5V 1.5V
8V 8V
8P
1.92P
3P



Your help is appreciated.

Brett Hagen

 

[Pete_UK]

With your data in A17, put this array* formula in (for example) A10:

=SUM(IF((A17<>"")*(RIGHT(A17,1)="P"),VALUE((LEFT(A17,LEN(A17)-1)))))

It will give you the count for entries ending in "P" (i.e. 22.25).
This one in A11 will give you the count for those ending in "V" (i.e.
20.5):

=SUM(IF((A17<>"")*(RIGHT(A17,1)="V"),VALUE((LEFT(A17,LEN(A17)-1)))))

* An array formula must be committed using the key combination of CTRL-
SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly
then Excel will wrap the formula with curly braces { } when viewed in
the formula bar - you must not type these yourself. Use CSE again if
you subsequently edit/amend the formula.

Hope this helps.

Pete

 

[Brett Hagen]

Pete,

That worked perfectly!

Thank you.


With your data in A17, put this array* formula in (for example) A10:

=SUM(IF((A17<>"")*(RIGHT(A17,1)="P"),VALUE((LEFT(A17,LEN(A17)-1)))))

It will give you the count for entries ending in "P" (i.e. 22.25).
This one in A11 will give you the count for those ending in "V" (i.e.
20.5):

=SUM(IF((A17<>"")*(RIGHT(A17,1)="V"),VALUE((LEFT(A17,LEN(A17)-1)))))

* An array formula must be committed using the key combination of CTRL-
SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly
then Excel will wrap the formula with curly braces { } when viewed in
the formula bar - you must not type these yourself. Use CSE again if
you subsequently edit/amend the formula.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Brett - thanks for feeding back.

Pete