I am trying to find a solution to the following data - hav tried all sorts to
no avail
BRANCH DEPT ACCOUNT TOTAL
C SV 300 $450
W SV 310 $200
C PT 300 $500
W PT 300 $100
C SV 300 $ 50
C SV 310 $200

The list of data extends over about 5000 lines with many variations per column
What i am want to determine is a formula that will tell me the total sum of
the TOTAL column when it meets a certain criteria from the first 3 columns -
ie what is the total $ where the branch is C - Department is SV - and the
Account is 300 (answer is $450 + $50=$500) - any ideas please.

First, there were some typos in my earlier response. It should have read as
F1, F2:H2, etc instead of E1, E2:H2, etc. Sorry about that.

Ok, as for:
.. to look at all account numbers say from 300 to 305
(thus excluding all others outside a range ..

Use something like this instead in I1:
=SUMPRODUCT((A$2:A$7=F1)*(B$2:B$7=G1)*(C$2:C$7=30 0)*(C$2:C$7<=305),D$2:D$7)

ie just "add in" the criteria as: ..*(C$2:C$7=300)*(C$2:C$7<=305)

And as for this:
.. to look only at account 300 as well as only account 205 [corrected]

You could use something like this in I1:
=SUMPRODUCT((A$2:A$7=F1)*(B$2:B$7=G1)*(ISNUMBER(MA TCH(C$2:C$7,{300;205},0))),D$2:D$7)

"Add-in" the criteria as: ..*(ISNUMBER(MATCH(C$2:C$7,{300;205},0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dubbo" wrote:
Great - works just as I require - 1 small variation though that you may be
able to help with - it is unlikely that the "account" will change once set so
I would like to set it into the formula - which is simple enough if it only
has to look at one account code - but how would I include in the formula if I
want it to look at all account numbers say from 300 to 305 (thus excluding
all others outside a range.

Similarly if I want it to look only at account 300 as well as only account
305

(understand that I do not have a 205 in my example - but say I do)

appreciate your assistance

Assuming data within A2:D7

Inputs set:
In E1: C, In G1: SV, In H1: 300

Then placed in I1:
=SUMPRODUCT((A$2:A$7=F1)*(B$2:B$7=G1)*(C$2:C$7=H1) ,D$2:D$7)
will return the required: 500 from D2:D7

I1 can be copied down to return correspondingly for other input sets in
E2:H2, E3:H3 etc. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dubbo" wrote:
I am trying to find a solution to the following data - hav tried all sorts to
no avail
BRANCH DEPT ACCOUNT TOTAL
C SV 300 $450
W SV 310 $200
C PT 300 $500
W PT 300 $100
C SV 300 $ 50
C SV 310 $200

The list of data extends over about 5000 lines with many variations per column
What i am want to determine is a formula that will tell me the total sum of
the TOTAL column when it meets a certain criteria from the first 3 columns -
ie what is the total $ where the branch is C - Department is SV - and the
Account is 300 (answer is $450 + $50=$500) - any ideas please.

Great - works just as I require - 1 small variation though that you may be
able to help with - it is unlikely that the "account" will change once set so
I would like to set it into the formula - which is simple enough if it only
has to look at one account code - but how would I include in the formula if I
want it to look at all account numbers say from 300 to 305 (thus excluding
all others outside a range.

Similarly if I want it to look only at account 300 as well as only account
305

(understand that I do not have a 205 in my example - but say I do)

appreciate your assistance


"Max" wrote:

Assuming data within A2:D7

Inputs set:
In E1: C, In G1: SV, In H1: 300

Then placed in I1:
=SUMPRODUCT((A$2:A$7=F1)*(B$2:B$7=G1)*(C$2:C$7=H1) ,D$2:D$7)
will return the required: 500 from D2:D7

I1 can be copied down to return correspondingly for other input sets in
E2:H2, E3:H3 etc. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dubbo" wrote:
I am trying to find a solution to the following data - hav tried all sorts to
no avail
BRANCH DEPT ACCOUNT TOTAL
C SV 300 $450
W SV 310 $200
C PT 300 $500
W PT 300 $100
C SV 300 $ 50
C SV 310 $200

The list of data extends over about 5000 lines with many variations per column
What i am want to determine is a formula that will tell me the total sum of
the TOTAL column when it meets a certain criteria from the first 3 columns -
ie what is the total $ where the branch is C - Department is SV - and the
Account is 300 (answer is $450 + $50=$500) - any ideas please.

Welcome, Dubbo.
Thanks for feeding back
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dubbo" wrote in message
...
Again - Thanks Max - This is first time I have used this forum - your
prompt reply has helped enormously