Sum multiple arrays of data according to criteria

[bmatheson]

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

 

[Ron Coderre]

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

 

[Ron Coderre]

Additional info:
If there may be text in the sum range, then try these ARRAY FORMULAS
(committed with Ctrl+Shift+Enter, instead of just Enter):

Using my previous post's example....
The Ford total
G1: =SUMPRODUCT(($A$2:$A$9=J1)*IF(ISNUMBER($B$2:$H$9),$B$2:$H$9))

or....the non "deleted" total
=SUMPRODUCT(($A$2:$A$9<>"deleted")*IF(ISNUMBER($B$2:$H$9),$B$2:$H$9))

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

XL2003, WinXP

 

[bmatheson]

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










- 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.

 

[Ron Coderre]

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<>"Comments")*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<>"Comments")*$B$2:$H$9)

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

XL2003, WinXP

 

[bmatheson]

Hmmm....I guessed the other way...that the Comments columns would containtext.

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<>"Comments")*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<>"Comments")*$B$2:$H$9)

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

XL2003, WinXP









- 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?

 

[Ron Coderre]

The simplest approach may be to simply flag the columns to be skipped by
entering the word "skip" in a row above or below the data.....

In this example, I inserted a row above the data and entered "skip" in cells
B1, E1 and F1:
=SUMPRODUCT(($A$3:$A$10=J2)*($B$1:$H$1<>"skip")*$B$3:$H$10)

Alternatively, you can specify which columns to skip within the formula.
If the list is short.....in this case, I skip Columns 5 and 6:
G1:
=SUMPRODUCT(($A$2:$A$9<>"deleted")*ISNA(MATCH(COLUMN($B$2:$H$9),{5;6},0))*$B$2:$H$9)

or....if the list is varied and long, put the list of column numbers in a
range off to the side (I'll use P110) and use this formula:
G1:
=SUMPRODUCT(($A$2:$A$9<>"deleted")*ISNA(MATCH(COLUMN($B$2:$H$9),$P$1:$P$10,0))*$B$2:$H$9)

Am I helping yet?
***********
Regards,
Ron

XL2003, WinXP

 

[bmatheson]

The simplest approach may be to simply flag the columns to be skipped by
entering the word "skip" in a row above or below the data.....

In this example, I inserted a row above the data and entered "skip" in cells
B1, E1 and F1:
=SUMPRODUCT(($A$3:$A$10=J2)*($B$1:$H$1<>"skip")*$B$3:$H$10)

Alternatively, you can specify which columns to skip within the formula.
If the list is short.....in this case, I skip Columns 5 and 6:
G1:
=SUMPRODUCT(($A$2:$A$9<>"deleted")*ISNA(MATCH(COLUMN($B$2:$H$9),{5;6},0))*$­B$2:$H$9)

or....if the list is varied and long, put the list of column numbers in a
range off to the side (I'll use P110) and use this formula:
G1:
=SUMPRODUCT(($A$2:$A$9<>"deleted")*ISNA(MATCH(COLUMN($B$2:$H$9),$P$1:$P$10,­0))*$B$2:$H$9)

Am I helping yet?
***********
Regards,
Ron

XL2003, WinXP









- Show quoted text -

that's great Ron. Thanks for your help with this.