On 24 Jul, 18:04, Ron Coderre
wrote:
Hmmm....I guessed the other way...that the Comments columns would contain text.

See if these ARRAY FORMULAS (that skip the comments column completely) help:

If there may be text anyplace in the sum range
G1:
=SUMPRODUCT(($A$2:$A$9=J1)*($B$1:$H$1<"Comments") *IF(ISNUMBER($B$2:$H$9),$*B$2:$H$9))
or
=SUMPRODUCT(($A$2:$A$9<"deleted")*($B$1:$H$1<"Co mments")*IF(ISNUMBER($B$2*:$H$9),$B$2:$H$9))

OR.....if there will NEVER be text in the sum range,
then try thes REGULAR FORMULAS (committed with just Enter):
G1: =SUMPRODUCT(($A$2:$A$9=J1)*($B$1:$H$1<"Comments") *$B$2:$H$9)
or
=SUMPRODUCT(($A$2:$A$9<"deleted")*($B$1:$H$1<"Co mments")*$B$2:$H$9)

Does that help?
***********
Regards,
Ron

XL2003, WinXP



" wrote:
On 24 Jul, 17:38, Ron Coderre
wrote:
With your posted table of information in cells A1:H9

This formula returns the total values for Ford
F1: Ford
G1: =SUMPRODUCT(($A$2:$A$9=J1)*$B$2:$H$9)

OR...if you want the total of all rows that do not begin with "delted"
G1: =SUMPRODUCT(($A$2:$A$9<"deleted")*$B$2:$H$9)

Is that something you can work with?

(Post back if you have more questions.)
***********
Regards,
Ron

XL2003, WinXP

" wrote:
I have had a good look through the posts but haven't found a problem
quite like this, though I am sure it has come up before.

Are you able to obtain a single result for the following problem.

For each car manufacturer, sum all the months of data, excluding the
comment columns, for any row that does not contain the phrase
deleted. But I don't want to use a pivot table or VBA.

May June JulyComments Comments Aug Sep
ford 12 1 101 102 87
ford 168 2 102 87 16
chrysler 44 5 105 74 31
chrysler 54 3 118 166 18
deleted 73 7 147 518 35
deleted 89 9 198 2 68
ferrari 28 5 187 87 18
ferrari 46 1 185 89 82

My problem is how to sum the row without the columns in the middle
which cannot be moved. And how to search and sum row by row without
using VBA.

thanks in advance- Hide quoted text -

- Show quoted text -

thanks Ron

Your formula works well for the table above. However, I drew up this
table a little hastily. The comments columns actually contain
numberical data. So the formula will include those columns, won't
it?

How do I exclude those columns?

Thanks again for your help.- Hide quoted text -

- Show quoted text -

Hi Ron

thanks again. I think my example wasn't as helpful as I had hoped.
It's more of an example, the actual workbook I have contains 150
columns of numerical data and there is a group of about 25 columns in
the middle which I want to exclude from the total. They have quite
longwinded and different column headings so the method you suggested
above wouldn't work.

To be more specific there are many columns of different quarterly
information and many rows of different contracts. Usually I would use
a pivot table to summarise this but in this case i need a singe result
returned by a formula. I want to sum those rows that do not contain
the phrase deleted, and exclude a big middle group of columns that
contain irrelevant quarterly data.

Is there any way to do this?