sumif and 2 criteria that dynamically moves?

[biagio85]

I have a complex formula that i am looking help with. Below is a similar layout.

Shipments
this account 1234 5 5 5
that account 5637 9 9 9
other account 8214 2 2 2

Production
little account 7512 5 5 5
small account 8785 9 9 9
that account 5637 3 3 3
big account 1562 2 2 2

the number of accounts change but sometimes there is the same accounts in both perscriptions and production. i need to dynamically find production and sum the 3 columns for that account. I will need to do the same for each account by title(shipments, production). I cannot add another column as a reference number and i cannot change the account numbers. Also as i said above the number of accounts changes so i cant say something like find production and go 3 cells down or anything like that.

Thanks for any help..

Ben

 

[Arcticwarrio]

=sumif(A2,7532,B2:B100)+sumif(A2,7532,C2:C100)+sumif(A2,7532,D2:D100)

 

[biagio85]

that will not work. what do you do whent you come across the same account number? also i have about 20 titles and accounts in each of them sometimes more then one appears in each.

 

[Arcticwarrio]

if you have the same account number more than once how do you know which one to use?

 

[biagio85]

thats my problem. i need it to look at the title such as production and look down until the next title or something like that.

 

[Arcticwarrio]

you could always have shipments starting in column A and production starting in column F or something that way it will always start in row 2, then you can use VLOOKUP wich always looks at the topmost occurance