Help with conditions in formula

Strawberrydelight

New Member
Joined
Jun 26, 2019
Messages
9
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.
 
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
 
Last edited:
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
 
Upvote 0
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:

Code:
=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.

Code:
=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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top