SUMIF and AND

GaryHull

New Member
Joined
Oct 15, 2002
Messages
9
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Mar 13, 2002
Messages
555
Office Version
  1. 365
Platform
  1. Windows
Hello GaryHull

You can use the sumproduct function

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

hth
 

GaryHull

New Member
Joined
Oct 15, 2002
Messages
9
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

many thanks for the help. It works fine.

GH

& no apologies? :biggrin:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

Forum statistics

Threads
1,144,148
Messages
5,722,768
Members
422,456
Latest member
lipa

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top