Sum Text Fields

  • Thread starter Thread starter Brett Hagen
  • Start date Start date
B

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
 
With your data in A1:D7, put this array* formula in (for example) A10:

=SUM(IF((A1:D7<>"")*(RIGHT(A1:D7,1)="P"),VALUE((LEFT(A1:D7,LEN(A1:D7)-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((A1:D7<>"")*(RIGHT(A1:D7,1)="V"),VALUE((LEFT(A1:D7,LEN(A1:D7)-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,

That worked perfectly!

Thank you.


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

=SUM(IF((A1:D7<>"")*(RIGHT(A1:D7,1)="P"),VALUE((LEFT(A1:D7,LEN(A1:D7)-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((A1:D7<>"")*(RIGHT(A1:D7,1)="V"),VALUE((LEFT(A1:D7,LEN(A1:D7)-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
 
Back
Top