Help with conditions in formula

[Strawberrydelight]

Hi, i'm looking to find the sum of two columns (that are not next to each other) based on the conditions of a third column. I am trying to do a final report of finances based on cost centres (in the third column), and the amounts are in two columns, cash and bank.

I'm not sure how to attach an image without using a url, so if anyone needs to see what I mean, and can tell me how to attach an image - I can do that. Thanks in advance.

 

[kweaver]

Based on my sample data, what did you expect the sum to be?

I'm summing ALL of column P with only those in column K that are > 0 and they must be in "Center2". Column P is not included when K is <=0

 

[Strawberrydelight]

It should be adding two long columns of figures, so I would expect the sum to be around 1,700

 

[mole999]

Mm, that hasn't worked either. If I could attach an image, it would probably be easier, but I don't have a URL for it. And I can't find a way of just attaching the image to show you.

attachments are barred on the site as per the terms that new joiners are referred to

 

[Strawberrydelight]

The problem could be that the E column is on a different page to the K and P columns?

 

[Strawberrydelight]

It needs to be where column Kis >0 and also adding column P if that is also >0

 

[kweaver]

Since my formula checked for "Center2" and uses only those values where K>0, the last row (-10, 1000, Center2) is not under consideration for the sum.
So, the sum is: 20+200, 30+300, 50+500, 90+900 which is 2090.

If I change "Center2" to "Center1" the sum is: 900 because the -40 and 400 are excluded.

In my sample data, NONE of the P column is negative.

This adjustment will look at column P for only positive values:

=SUM(((U2:U11="Center1"))*((K2:K11>0)*((P2:P11>0)*(K2:K11+P2:P11))))

I made the 800 in column P a -800 for Center1 and now the sum is only the first row = 10+100 = 110.
I actually don't need all those parens, but sometimes it helps with clarity.

=SUM((U2:U11="Center1")*(K2:K11>0)*(P2:P11>0)*(K2:K11+P2:P11))

The different tab isn't an issue as long as you identify that in the formula such as: Sheet2!P2:P11>0

AND, the ranges must all be the same.

 

[Strawberrydelight]

Thank you for helping. I'm just about to leave work now, so will try it again tomorrow. Will keep you posted.