Excel Conditional Summing

[SteveM4274]

Hi

I'm having a problem applying a sumif formula which contains to conditions. I have a spreadsheet which extracts transactions from a database every week so the spreadsheet is continually growing. I have another spreadsheet which should summarise the data by dept depending on the date selected by the user:

A B C D

1 Date

2 Dept 1 xx

3 Dept 2 xx

4 Dept 3 xx

I have used the following formula:

=SUM(IF((DataSpreadsheet!A:A=DeptAnalysis!$A$1)+(DataSpreadsheet!C:C="Dept 1"),DataSpreadsheet!I:I))

However this is returning the total value of all cells in column I. I have also tried using the conditional sum wizard but this does not allow me to select columns, it requires cells with data to be selected which is no use since I want the spreadsheets to automatically summarise when new rows are added.

I hope this makes sense and I would appreciate any help.

 

[SkipVought]

Hi,

Try this...
[tt]
=SUMPRODUCT((DataSpreadsheet!A:A=DeptAnalysis!$A$1)*(DataSpreadsheet!C:C="Dept 1")*(DataSpreadsheet!I:I))
[/tt]

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[yogia]

Hi SteveM4274:

I believe you need a clearer explanation of what exactly you are trying to accomplish. Please describe clearly in words without the use of any formulas, so we can clearly understand the logic.

The syntax of the formula you posted

=SUM(IF((DataSpreadsheet!A:A=DeptAnalysis!$A$1)+(DataSpreadsheet!C:C="Dept 1"),DataSpreadsheet!I:I))

is not right.

For one you can not use the whole column as a range in the
=SUM(IF( ... type formulation,

and ....
-------------------------------------
Let me have you try the following ...

=SUM(IF(DataSpreadsheet!A1:A65535=DeptAnalysis!$A$1,IF(DataSpreadsheet!C1:C65535="Dept 1",DataSpreadsheet!I1:I65535)))

Does the preceding formula give you the result you are looking for? In any event, please post back with more details and then let us take it from there.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[Rofeu]

Hi,

I'm a big fan of SUMPRODUCT. If you need to use many of those though, I suggest to preceed each (criterion) with a double negative --. this coerces the boolean into an integer and will result in faster calculation.

Cheers,

Roel

 

[SteveM4274]

Thanks for your reply. The SUMPRODUCT seems to be the way forward