Excel Question: SUMIF with multiple ranges

[Dreadnaught]

Is it possible to have a SUMIF total the values in multiple sets of ranges?

EG I currently have the following formula-

=SUMIF('Sheet2'!$A$5:$C$289,'Sheet1'!$A$4:$A$42,'S heet2'!$B$5:$B$289)

This formula checks if there are values in Sheet2, Column A that match the value of a specific line in Sheet1, Column A.

Sheet 2 is arranged like this:

A         |  B
Client 1 |  $4
Client 1 |  $3
Client 2 |  $65
Client 1 |  22

If there is a match, every item in column B that matches that criteria is added-up into one nice, neat cell.

However my data set has changed so that I have to sum-up several non-contiguous cells from the following data set:

A         |  B  [.....] | F
Client 1 |  $4         | $23
Client 1 |  $3         | $3
Client 2 |  $65       | $23
Client 1 |  22         | $83

For reasons beyond my control (I blame government), there's no way I can change these datasets to have the ranges of numbers to be summed-up so that they are grouped next to each other.

I can do multiple SUMIFs in one cell, but I have to add-up six different non-contiguous columns so the formula is really huge.

Any Excel wizards have any more elegant solutions? I should also mention I'm doing this in Google Docs, but the formulas are mostly the same.

[Nessus]

Use Matlab

[RandBlade]

Excel '07 onwards can do this incredibly easily. Excel '97-'03 couldn't easily, you'd have to do it in a more ugly manner.

Easiest way using '97-'03 would be to have two columns, one that sums the data you need for each row (it doesn't need to be part of the dataset) and then another that is the sumif.

[Timbuk2]

I have no idea. I have computer science graduates do this lowly stuff for me.

[Dreadnaught]

Excel '07 onwards can do this incredibly easily. Excel '97-'03 couldn't easily, you'd have to do it in a more ugly manner.

Easiest way using '97-'03 would be to have two columns, one that sums the data you need for each row (it doesn't need to be part of the dataset) and then another that is the sumif.

Using the latest version, it's not easy according to me and two people who know a lot more about Excel than I do.

You can certainly apply a sumif to a range of columns, but the range has to be a contiguous set of columns. These columns aren't next to each other and for stupid reasons can't be next to each other.

[RandBlade]

I was thinking of the new Sumifs formula that allows multiple conditions, which wasn't allowed under Sumif.

Why not just have another column that applies to the other columns before you sum it? Do I understand it right? You want to get the sumif totals of columns like in my quickly made up example Column E and Column H?

[Dreadnaught]

Yes, I think we're on the same page. Somehow no one I've spoken to has heard of Sumifs...though I have a feeling that formula may not work in Google Docs