SUMIF with two conditions 1

[ralphralph07]

Hi

I'm trying to do a sum if with two conditions and have the following formula - but it's picking up values that don't exist - for example I only have one section that should pull back data but it's bringing data back for other sections?

Not sure where i'm going wrong on this one.

i have set the formula to be an array.

=SUM(IF(('Working Data'!$A$2:$A$2000='Average By Data'!$A2)+('Working Data'!$G$2:$G$2000='Average By Data'!$B2),'Working Data'!K2:K2000,0))/$C2

 

[GlennUK]

Your logic is wrong. You'd be better off doing this through the Condition Sum add-in. ( once added in, do Tools/Conditional Sum and follow instructions )

Or use SUMPRODUCT as shown here:

http://www.tek-tips.com/viewthread.cfm?qid=720840

( array formula with correct formula would be:

=SUM(IF(('Working Data'!$A$2:$A$2000='Average By Data'!$A2),IF('Working Data'!$G$2:$G$2000='Average By Data'!$B2,'Working Data'!K2:K2000,0),0))/$C2

... not tested )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[ralphralph07]

Hi Glenn

Thanks for the information but I'm still having trouble getting this to work - I have added the formula above but it's missing out some of the functions.

I tried the SUMPRODUCT but that gave me very strange results.

I'm i missing something else?

Ralph

 

[GlennUK]

Can you give an example of typical data, together with whatever results you think the formulae should produce?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[ralphralph07]

Hi Glenn

Apologies that would help wouldn't it!

Working Data Sample:

Department Head Count Type Jan Feb Mar
A 14 A 10 10 10
A 14 A 10 10 10
A 14 E 5 5 5
A 14 E 5 5 5
B 7 A 5 5 0
B 7 E 6 6 0

What the Average sheet needs to do is find all departments A and Type A and Sum them and then divide by Head Count to give me an average per month.

The what the SUM function is doing is not giving me anything back for the E's and the values not match.

Hope this helps I could send the actual file if easier?

Ralph

 

[GlennUK]

What the Average sheet needs to do is find all departments A and Type A and Sum them and then divide by Head Count to give me an average per month.

Please explain what your data is showing for head count ... is this an amount that should be summed before being used for division?

The what the SUM function is doing is not giving me anything back for the E's and the values not match.

What is the formula giving you, and what figure were you expecting? Er, "the values not match" doesn't make any sense to me.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[ralphralph07]

Hi Glenn

The head count figure is pulled through from another sheet and is the total people work for that department - so it has already been summed.

The SUM function isn't finding all the A Department and A Types - if I sort the working data and do manually i get a different number.

For example:

Department Head Count Type Jan Feb Mar
A 14 A 10 10 10
A 14 A 10 10 10
A 14 E 20 20 20
B 7 A 10 10 10
B 7 E 10 10 10
B 7 E 20 20 20

If I use the following formula the result is 5.71

So it's actually just adding all column D to get to 80?

=SUM(IF((A12:A17="A"),IF(C12:C17="A",D1217,0),0))

So its seems to be ignoring the IF Statements?

I hope this helps?

Ralph

 

[ralphralph07]

Hi

I looked at it again and see why it's summing D1217 because that's what I've told it to do.

BUT I only need it to some where the Department = A and the Type = A

Is this possible?

Thanks

Ralph

 

[bluedragon2]

=SUMPRODUCT((A12:A17="A")*(C12:C17="A")*(D1217))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[GlennUK]

Use BlueDragon's formula.

But, just out of curiosity, you said:

If I use the following formula the result is 5.71

So it's actually just adding all column D to get to 80?

=SUM(IF((A12:A17="A"),IF(C12:C17="A",D1217,0),0))

So its seems to be ignoring the IF Statements?

I copied your table to a sheet, and using that formula I get 20. ( which is what BlueDragon's formula will give you too ). Now what are you doing to get 5.71? I don't know.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.