ExcelSum Column Containing numeric and alpha text

[Chris Waller]

Hi,

I am trying to create a sum formula that will sum a column which contains a mixture of numeric and numeric an text entries. I am using Excel 2016 and I have a column of financial figures, which I know I can use the =sum function to calculate the total of these figures, however, some of the entries include the text "inc VAT" and "exc VAT". What I would like to do is sum all the "inc VAT" entries all the "exc VAT" entries and create a grand total which is the sum of all three categories. Had I made the Spreadsheet I would have put the text in the next column, however, as this has come from another source and there is a tight deadline, I was wondering if anyone could give any tips?

 

[Eric W]

Try:

Book2
	A	B	C	D	E	F
1	Data		No text	inc VAT	exc VAT	Total
2	1		21	11	13	45
3	2
4	3
5	4 inc VAT
6	5 exc VAT
7	6
8	inc VAT 7
9	exc VAT 8
10	9
11
Sheet4

Cell Formulas
Range	Formula
C2	C2	=SUM(A2:A20)
D2	D2	=SUMPRODUCT(IFERROR(--SUBSTITUTE(A2:A20,"inc VAT",""),0))-C2
E2	E2	=SUMPRODUCT(IFERROR(--SUBSTITUTE(A2:A20,"exc VAT",""),0))-C2
F2	F2	=SUM(C2:E2)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Chris Waller]

Eric,

Thanks for your prompt reply. I did try this formula this morning and it did work as you suggested. Great, Kudos to you.

 

[Eric W]

Glad it works for you! Thanks for the feedback.