Count and Sum of cells if conditions fulfilled

[Bono]

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!

 

[JE McGimpsey]

One way:

=COUNTIF(A1:A100, "Yes")

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

 

[Max]

=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

 

[Mike H]

Hi,

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

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

Mike

 

[Bono]

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

 

[Bono]

Sorry, submitted before reading, I meant

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

Thanks

 

[Max]

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
---

 

[Max]

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]

Pressed the "Yes" button, thanks for help.

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

 

[Max]

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
---

 

[David Biddulph]

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

 

[Bono]

Thanks a lot. Still learning

Welcome, and thanks

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

 

[Max]

Welcome, steam on ...
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik