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.

 

[mole999]

images/documents/attachments can be referenced from a open reputable cloud server, many won't visit due to work access or the number of hoaxes, and any site that requires registration is basically out as well.

You can however post html tables if properly configured, check my signature line for links and info

 

[Strawberrydelight]

The formula looks like this:

=SUM(IF('Entry Sheet'!$E$19:$E$652='Entry Sheet'!U54,IF('Entry Sheet'!$K$19:$K$652>0,'Entry Sheet'!$K$19:$K$652),0),0)

and I want it to also add column 'P' as well as 'K'.

 

[kweaver]

A slightly different approach with the data to be summed in columns M and N and the criteria in O:

=SUM((M2:M11+N2:N11)*(--(O2:O11="Center2")))

This is an array formula -- enter with CTRL+Shift+Enter

 

[kweaver]

You can adapt my array formula with whatever columns you want inside the first set of parens.

 

[Strawberrydelight]

The first part of my forula is the condition - If everything in column E = the codes on U54, and if everything in column K is more than 0, then add up column K. I want it to include adding column P with the same conditions. I've just tried your code, but it hasn't worked. Just trying it again. I've replaced M and N with K and P, and 0 with E - taking it that's what I was supposed to do.

 

[mole999]

confirm you are using CSE to finalise ?

 

[Strawberrydelight]

Yes. I'm using CSE to finalise and it accepts the formula but returns 0

 

[kweaver]

I didn't try to create your large file, but hopefully can can adapt this modified array formula:

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

You can probably get away without the double minus. I'm just used to being safe rather than sorry.

The "center" criteria is first as you had it, then the test if the K column is >0 and it's added to the P column. With this data, the sum is 2090.

<tbody>
[TD="class: xl66"]K[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, width: 23"][/TD]
[TD="class: xl66, width: 23"][/TD]
[TD="class: xl66, width: 64"]P[/TD]
[TD="class: xl66, width: 25"][/TD]
[TD="class: xl66, width: 25"][/TD]
[TD="class: xl66, width: 25"][/TD]
[TD="class: xl66, width: 25"][/TD]
[TD="class: xl66, width: 64"]U[/TD]

[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Center1[/TD]

[TD="class: xl65, align: right"]20[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Center2[/TD]

[TD="class: xl65, align: right"]30[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]300[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Center2[/TD]

[TD="class: xl65, align: right"]-40[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]400[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Center1[/TD]

[TD="class: xl65, align: right"]50[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]500[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Center2[/TD]

[TD="class: xl65, align: right"]60[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]600[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Center3[/TD]

[TD="class: xl65, align: right"]70[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]700[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Center4[/TD]

[TD="class: xl65, align: right"]80[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]800[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Center1[/TD]

[TD="class: xl65, align: right"]90[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]900[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Center2[/TD]

[TD="class: xl65, align: right"]-10[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1000[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Center2[/TD]

</tbody>

 

[Strawberrydelight]

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.