# SUMIF and AND

#### GaryHull

##### New Member
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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

##### MrExcel MVP
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

##### Well-known Member
Hello GaryHull

You can use the sumproduct function

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

hth

#### GaryHull

##### New Member
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

##### MrExcel MVP
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

#### GaryHull

##### New Member

many thanks for the help. It works fine.

GH

##### MrExcel MVP
On 2002-10-29 07:59, GaryHull wrote:

many thanks for the help. It works fine.

GH

& no apologies?

#### GaryHull

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

GH

##### MrExcel MVP
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.

Replies
8
Views
271
Replies
7
Views
130
Replies
2
Views
72
Replies
3
Views
83
Replies
0
Views
48

1,181,900
Messages
5,932,700
Members
436,852
Latest member
Borekch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back