| 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
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! |
|
#5
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! |
|
#6
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... |
|
#7
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... |
|
#8
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 --- |
|
#9
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! |
| 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 | |||
Hybrid Mode
