SUMIF and AND

[GaryHull]

I need to insert a SUMIF and AND into a function to test two ranges of cells for correct response.

I cannot see how to combine the SUMIF and the AND functions.

For example:

sum cells a1:a55 if cell b1:b55 contain '1' and cell c1:c55 contains 'y'

Anyone help?

Thanks

 

[Aladin Akyurek]

On 2002-10-29 02:49, GaryHull wrote:
I need to insert a SUMIF and AND into a function to test two ranges of cells for correct response.

I cannot see how to combine the SUMIF and the AND functions.

For example:

sum cells a1:a55 if cell b1:b55 contain '1' and cell c1:c55 contains 'y'

Anyone help?

Thanks

=SUMORODUCT(($B$1:$B$55=1)*($C$1:$C$55="y"),$A$1:$A$55)

 

[sen_edp]

Hello GaryHull

You can use the sumproduct function

=SUMPRODUCT((B1:B55=1)*(C1:C55="y")*A1:A55)

hth

 

[GaryHull]

Thanks Andreas, but it was not quite the answer I was looking for.

The function that I am trying to write is:-

=SUMIF($F$8:$F$55,"y", AND $C8:$C55, '1',$T$8:$T$55)

to explain.

Check cells $F$8:$F$55, and see if if any of the cells have a 'y' in them, then check cells $C8:$C55 to see if any of them have a '1' in them, then summate the cells in $T$8:$T$55 which have values in them and are selected on the basis of having both a 'y' and a '1' in them.

Hope that helps

Thanks

 

[PaddyD]

which is exactly what the suggestions do if you changes the ranges to suit your data:

=sumproduct(($F$8:$F$55="y")*(C8:$C55=1)*($T$8:$T$55))

see

http://216.92.17.166/board/viewtopic.php?topic=20832&forum=2

for more info - follow the link in the last contrib.

paddy

 

[GaryHull]

Paddy,

many thanks for the help. It works fine.

GH

 

[Aladin Akyurek]

On 2002-10-29 07:59, GaryHull wrote:
Paddy,

many thanks for the help. It works fine.

GH

& no apologies?

 

[GaryHull]

Sorry, I was not aware that I had upset any one.

GH

 

[Aladin Akyurek]

On 2002-10-29 08:09, GaryHull wrote:
Sorry, I was not aware that I had upset any one.

GH

Upset? No. I'm hinting at the fact that you could re-check the initial replies to see whether they were sensible. Such a check would help you to better understand the solution structure.

Aladin