Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to count relying upon conditions in other cells | Excel Discussion (Misc queries) | |||
"Count If" 3 criterias are fulfilled | Excel Discussion (Misc queries) | |||
Count Cells if diff cell meets conditions | Excel Worksheet Functions | |||
Count the number of Cells in one ROW with conditions | Excel Worksheet Functions | |||
count number of cells based on TWO conditions (2 different columns | Excel Worksheet Functions |