Sumif with two conditions help

[markdenovan]

Hi,

I have a formula which I think should work:

{=SUM(('Blue Cell Data'!$B$4:$B$108='A Shift'!D9)*('Blue Cell Data'!$C$4:$C$108="A")*('Blue Cell Data'!$I$4:$I$108))}

Blue Cell Data!$B$4:$B$108 = custom date dd/mm/yyyy
A Shift!D9 = a custom date dd/mm/yyyy
Blue Cell Data!$C$4:$C$108 = General Text
Blue Cell Data!$I$4:$I$108 = Number

It should work, the values are there to be picked up, but all I get is #value!

Any ideas?

Many thanks,

Mark

 

[Aladin Akyurek]

...

{=SUM(('Blue Cell Data'!$B$4:$B$108='A Shift'!D9)*('Blue Cell Data'!$C$4:$C$108="A")*('Blue Cell Data'!$I$4:$I$108))}

Blue Cell Data!$B$4:$B$108 = custom date dd/mm/yyyy
A Shift!D9 = a custom date dd/mm/yyyy
Blue Cell Data!$C$4:$C$108 = General Text
Blue Cell Data!$I$4:$I$108 = Number

It should work, the values are there to be picked up, but all I get is #value!

...

Does the equivalent, to be confirmed with just enter,...

=SUMPRODUCT(--('Blue Cell Data'!$B$4:$B$108='A Shift'!D9),--('Blue Cell Data'!$C$4:$C$108="A"),('Blue Cell Data'!$I$4:$I$108))

work?

 

[markdenovan]

If I do that I get #N/A ?

Mark

 

[Aladin Akyurek]

If I do that I get #N/A ?

Mark

Does the range

'Blue Cell Data'!$I$4:$I$108

contain any #N/A?

 

[markdenovan]

No, just numbers and blanks?

Mark

 

[markdenovan]

There was in the A column though.

Many thanks for all your help,

Mark

 

[Aladin Akyurek]

There was in the A column though.

Many thanks for all your help,

Mark

Right. If it's computed by a formula, make that formula return something else like "".