Cell Match and Sum problem

[Isabelle14]

Match 2 criteria in 2 separate columns of a Database and Sum up the respective values in a 3rd column?

I have a database table which extracts information from a legacy system in a fixed format. I would like to find a formula that matches criteria in 2 separate columns in the database table, and then proceeds to sum up the equivalent rows in the 3rd column.

I will apply the formula to build up a cross tab table which can then lend itself nicely to reporting and charting. Therefore a Pivot table solution is not appropriate in this case, and DSUM does not feel right as it requires a new criteria for every cell in the cross tab.

Any insights will be greatly appreciated.

[stanleydgromjr]

Isabelle14,

Welcome to the Excel Forum.

You may be able to use SUMPRODUCT.

Can we see your workbook? Click on the New Post button, then scroll down an click on the Manage Attachments button.

[TMS]

Sounds like you could use SUMPRODUCT.

For example:

=SUMPRODUCT(--($G$2:$G$27="first check"),--($H$2:$H$27="second check"),--($I$2:$I$27))

Tests cells in columns G and H and sums matching values in column I

Regards

[DonkeyOte]

@TMShucks - unless all of the values in I2:I27 are numbers stored as text the double unary coercion of said range is not required (desired).

[TMS]

@DonkeyOte ... good call, force of habit. But, with little evidence to go on, who knows :-)

[DonkeyOte]

But, with little evidence to go on, who knows :-)

True but just to clarify my thinking:

One of the main advantages of using double unary method in SUMPRODUCT is that you avoid the need to explicitly coerce the summation range (or any numeric range for that matter).

In effect this means that SUMPRODUCT acts along the lines of a SUMIF in so far as non-numerics are simply ignored and do not lead to #VALUE! errors.

To elaborate by means of a very basic example:

A1:C4
a c 10
b a 20
a c incomplete
a b 30

=SUMPRODUCT(--(A1:A4="a"),--(B1:B4="c"),C1:C4) -> 10

=SUMPRODUCT(--(A1:A4="a"),--(B1:B4="c"),--(C1:C4)) -> #VALUE!

If we avoid the coercion of C1:C4 the "incomplete" entry does not disrupt the output.

The above is not to say there is never a need to coerce the numeric ranges nor that a #VALUE! error would never be desired - it's simply that 9 times out of 10 the use of double unary is for this reason (or at least that holds true in my experience).

[Isabelle14]

Hi there
Sample of workbook attached.
The database is in one page and the "workings" tab is where I am attempting to build a Crosstab table from the database.

I have just tried SUMPRODUCT as advised by TMShucks but unfortunately get a #NUM! error? I have used named areas within this formula, but even writing it out without named areas I still get the error. (I hope I am not just typing the formula in incorrectly).

I still believe Excel can do this - thanks for your prompt replies I was quite taken aback.

Isabelle14

[DonkeyOte]

SUMPRODUCT is an inefficient formula so if you do use it do so with caution - ensure ranges are kept lean etc...

Prior to XL2007 you can not use SUMPRODUCT (or Arrays) with entire column references (T:T) -> #NUM! errors will result (as you describe)

Your defined Names are using whole column references.

For ex.

JCSPPeriod
='1Data Base'!$T:$T

You should look to make your Names a little more dynamic though I would opt for a non-volatile construct given they are to be used in conjunction with inefficient formulae

JCSPeriod
='1DataBase'!$T$10:INDEX('1Database'!$T:$T,MATCH(9.99E+307,'1Database'!$A:$A))

repeat this construct for all other names in use (note commencement from row 10)

Finally - you must correct your SUMPRODUCT itself

=SUMPRODUCT((JCSPPeriod=A4),(Segment=B$3),(TonnesSold))

should be either:

=SUMPRODUCT(--(JCSPPeriod=A4),--(Segment=B$3),TonnesSold)

or

=SUMPRODUCT((JCSPPeriod=A4)*(Segment=B$3)*TonnesSold)

I prefer option 1 in this instance.

[Isabelle14]

WOW!
I cannot figure out the new code for creating a named area ( the underlined bit below:
='1DataBase'!$T$10:INDEX('1Database'!$T:$T,MATCH(9.99E+307,'1Database'!$A:$A))

but apart from that - following through your advice the SUMPRODUCT formula now works.

I am thrilled to have a solution. Many Thanks
Isabelle14

[DonkeyOte]

the section underlined establishes the row in which the last number appears in Column A - this value it seems is perhaps the best means of determining the last row of info. in your table.
Using the same section in each name means all the names share the same height dimension (ie all are sized based on Col A)

[Isabelle14]

Hello again,

Thanks for explaining the section underlined. Does this mean that if I add rows to the database table (as I intend to every month) I should edit the formula of the named area to update the "row in which the last number appears in Column A "