Count and Sum of cells if conditions fulfilled
 

Hi all,

Trying to count or sum values from a range, if a condition is respected:
A B
Yes 25
No 12
Yes 11

So, I would like in cells being able to:
- count cells if cell A is Yes (expecting then: count = 2, as A1 and A3 are
yes, so B1 and C1 are counted)
- sum cells if cell A is Yes (expecting then: sum= 36, as A1 and A3 are yes,
so B1=25 + C1=11 are summed=36)

Hope it's clear enough... thanks for help!

Count and Sum of cells if conditions fulfilled
 

One way:

=COUNTIF(A1:A100, "Yes")

=SUMIF(A1:A100, "Yes", B1:B100)

In article ,
Bono wrote:

Hi all,

Trying to count or sum values from a range, if a condition is respected:
A B
Yes 25
No 12
Yes 11

So, I would like in cells being able to:
- count cells if cell A is Yes (expecting then: count = 2, as A1 and A3 are
yes, so B1 and C1 are counted)
- sum cells if cell A is Yes (expecting then: sum= 36, as A1 and A3 are yes,
so B1=25 + C1=11 are summed=36)

Hope it's clear enough... thanks for help!

Count and Sum of cells if conditions fulfilled
 

=COUNTIF(A:A,"Yes")
=SUMIF(A:A,"Yes",B:B)

Similarly for "No"
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"Bono" wrote:
Trying to count or sum values from a range, if a condition is respected:
A B
Yes 25
No 12
Yes 11

So, I would like in cells being able to:
- count cells if cell A is Yes (expecting then: count = 2, as A1 and A3 are
yes, so B1 and C1 are counted)
- sum cells if cell A is Yes (expecting then: sum= 36, as A1 and A3 are yes,
so B1=25 + C1=11 are summed=36)

Hope it's clear enough... thanks for help!

Count and Sum of cells if conditions fulfilled
 

Hi,

I'm struggling to understand where C1 comes into it but how about this

=SUMIF(A1:A3,"Yes",B1:B3)

Mike

"Bono" wrote:

Hi all,

Trying to count or sum values from a range, if a condition is respected:
A B
Yes 25
No 12
Yes 11

So, I would like in cells being able to:
- count cells if cell A is Yes (expecting then: count = 2, as A1 and A3 are
yes, so B1 and C1 are counted)
- sum cells if cell A is Yes (expecting then: sum= 36, as A1 and A3 are yes,
so B1=25 + C1=11 are summed=36)

Hope it's clear enough... thanks for help!

Count and Sum of cells if conditions fulfilled
 

this one is fine :-), thanks

To add a bit more complexity: How can I count in column that are equal to
"25" AND cell in A being "yes"?

Thanks again



"Max" wrote:

=COUNTIF(A:A,"Yes")
=SUMIF(A:A,"Yes",B:B)

Similarly for "No"
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"Bono" wrote:
Trying to count or sum values from a range, if a condition is respected:
A B
Yes 25
No 12
Yes 11

So, I would like in cells being able to:
- count cells if cell A is Yes (expecting then: count = 2, as A1 and A3 are
yes, so B1 and C1 are counted)
- sum cells if cell A is Yes (expecting then: sum= 36, as A1 and A3 are yes,
so B1=25 + C1=11 are summed=36)

Hope it's clear enough... thanks for help!

Count and Sum of cells if conditions fulfilled
 

Sorry, submitted before reading, I meant

I Want Cell in colum A = "yes" and in column B ="25"; count is 1...

Thanks


"Bono" wrote:


this one is fine :-), thanks

To add a bit more complexity: How can I count in column that are equal to
"25" AND cell in A being "yes"?

Thanks again



"Max" wrote:

=COUNTIF(A:A,"Yes")
=SUMIF(A:A,"Yes",B:B)

Similarly for "No"
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"Bono" wrote:
Trying to count or sum values from a range, if a condition is respected:
A B
Yes 25
No 12
Yes 11

So, I would like in cells being able to:
- count cells if cell A is Yes (expecting then: count = 2, as A1 and A3 are
yes, so B1 and C1 are counted)
- sum cells if cell A is Yes (expecting then: sum= 36, as A1 and A3 are yes,
so B1=25 + C1=11 are summed=36)

Hope it's clear enough... thanks for help!

Count and Sum of cells if conditions fulfilled
 

"Bono" wrote:
this one is fine :-), thanks
Welcome, please rate that response by pressing the YES button (like the one
below)

To add a bit more complexity: How can I count in column that are equal to
"25" AND cell in A being "yes"?

Then it goes into the realm of using sumproduct, eg:
=sumproduct((B2:B100=25)*(A2:A100="Yes"))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---

Count and Sum of cells if conditions fulfilled
 

I Want Cell in colum A = "yes" and in column B ="25"; count is 1...

Wonder if you received my response?
Then it goes into the realm of using sumproduct, eg:
=sumproduct((B2:B100=25)*(A2:A100="Yes"))

P/s: Col B is assumed to contain real numbers, not text numbers

Do remember to press the YES button below, won't you?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"Bono" wrote:
Sorry, submitted before reading, I meant
I Want Cell in colum A = "yes" and in column B ="25"; count is 1...

Count and Sum of cells if conditions fulfilled
 

Pressed the "Yes" button, thanks for help.

What if the column B is text... any solution in that case?


"Max" wrote:

I Want Cell in colum A = "yes" and in column B ="25"; count is 1...

Wonder if you received my response?
Then it goes into the realm of using sumproduct, eg:
=sumproduct((B2:B100=25)*(A2:A100="Yes"))

P/s: Col B is assumed to contain real numbers, not text numbers

Do remember to press the YES button below, won't you?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"Bono" wrote:
Sorry, submitted before reading, I meant
I Want Cell in colum A = "yes" and in column B ="25"; count is 1...

Count and Sum of cells if conditions fulfilled
 

Pressed the "Yes" button, thanks for help.
Welcome, and thanks

What if the column B is text... any solution in that case?
Equating to Text/text numbers requires double quotes, eg: ="Yes", ="25"

If you have mixed data (real numbers & text numbers) in col B
you could use either:

=SUMPRODUCT((B2:B100+0=25)*(A2:A100="Yes"))
where the +0 will coerce any text nums in col B to real nums, w/o impacting
any existing real nums

Or (the other way around):
=SUMPRODUCT((B2:B100&""="25")*(A2:A100="Yes"))
where the &"" will change any real nums in col B to text nums, w/o impacting
any existing text nums
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---

Count and Sum of cells if conditions fulfilled
 

If column B is text, put the required text string in quote marks in the
SUMPRODUCT formula, as you have for column A.
--
David Biddulph

"Bono" wrote in message
...

Pressed the "Yes" button, thanks for help.

What if the column B is text... any solution in that case?


"Max" wrote:

I Want Cell in colum A = "yes" and in column B ="25"; count is 1...

Wonder if you received my response?
Then it goes into the realm of using sumproduct, eg:
=sumproduct((B2:B100=25)*(A2:A100="Yes"))

P/s: Col B is assumed to contain real numbers, not text numbers

Do remember to press the YES button below, won't you?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"Bono" wrote:
Sorry, submitted before reading, I meant
I Want Cell in colum A = "yes" and in column B ="25"; count is 1...

Count and Sum of cells if conditions fulfilled
 

Thanks a lot. Still learning :-)

"Max" wrote:

Pressed the "Yes" button, thanks for help.
Welcome, and thanks

What if the column B is text... any solution in that case?
Equating to Text/text numbers requires double quotes, eg: ="Yes", ="25"

If you have mixed data (real numbers & text numbers) in col B
you could use either:

=SUMPRODUCT((B2:B100+0=25)*(A2:A100="Yes"))
where the +0 will coerce any text nums in col B to real nums, w/o impacting
any existing real nums

Or (the other way around):
=SUMPRODUCT((B2:B100&""="25")*(A2:A100="Yes"))
where the &"" will change any real nums in col B to text nums, w/o impacting
any existing text nums
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---

Count and Sum of cells if conditions fulfilled
 

Welcome, steam on ...
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"Bono" wrote in message
...
Thanks a lot. Still learning :-)