HELP) Sum if statement over multiple criteria and addition and subtraction

[MarcBK]

Hello all.

I am trying to run a sumif formula between Cells C6:E1000 and E6:E1000 to calculate the total if in cells B6:B1000 is equal to certain text. But also subtract cells D6:D1000 if B6:B1000 is equal the same text as before.

For example column total amount between C (1000) + E (25) if B = "um" then subtract D (200) if also B equal "um"

B6: "um"
C6: 1000
E6: 25
Subtracting D: 200

Hope that makes sense.

Many thanks for your help and guidance.

 

[RobP]

Hi Marc,

you might need to show some example of your data, or provide a little more clarity in terms of your goal for people to understand your problem.

From what I read, it looks like you have numbers in columns C, D and E. You wish to sum (C & E) the rows (6 for an example) if column B (cell B6) = "um". and you will subtract column D (cell D6 in this case ?)

	A	B	C	D	E
1		um	5	2	5
2			3	4	8
3		um	4	3	6

So it would add only the rows 1 & 3 in the fashion below ?
1 --> 5+5-2 = 8
2 3 4 8
3 --> 4+6-3 = 7

So your desired SUMIF formula would provide an answer of 8+7 = 15 ?

Is that correct ?
thanks
Rob

 

[Fluff]

How about

=SUMPRODUCT((B6:B1000="um")*(C6:E1000*{1,-1,1}))

 

[RobP]

@Fluff - so Ive looked everywhere to try and find some reference to your {} curly brackets in terms of execution, but failed. I'm afraid you're going to have to explain to me how its working if you don't mind.?

 

[Fluff]

It's an array that will multiply col C & E by 1 & col D by -1

 

[MarcBK]

Thank you Fluff!

=SUMPRODUCT((B6:B1000="um")*(C6:E1000*{1,-1,1}))

Worked a charm!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[MarcBK]

Glad we could help & thanks for the feedback.

One further question, if I was to extend this over multiple sheets to capture the same "um" data how is this possible? Would need to extend to sheet 2,3,4 etc.

 

[Fluff]

You would need to do each sheet separately & add them up.