Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif, multiple criteria
Hi all,
I'd really appreciate help with what is, I'm sure, a simple problem that I'm too stupid to solve! The problem - Using COUNTIF (I think), the problem is that it requires two criteria/conditions to be met: For the range to be counted (as 1, NOT 2), A1 must have a value of 0 AND B1 must be more than 1. ONLY if these conditions are meet should the range be counted as 1. Eagerly awaiting for reply, JS25 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif, multiple criteria
Hi,
Perhaps we could see a small amount of test data and the result you expect. Mike "JS25" wrote: Hi all, I'd really appreciate help with what is, I'm sure, a simple problem that I'm too stupid to solve! The problem - Using COUNTIF (I think), the problem is that it requires two criteria/conditions to be met: For the range to be counted (as 1, NOT 2), A1 must have a value of 0 AND B1 must be more than 1. ONLY if these conditions are meet should the range be counted as 1. Eagerly awaiting for reply, JS25 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif, multiple criteria
Certainly:
'A' 'B' Expect Result - 0 0 0 0 1 0 0 2 1 0 3 1 1 0 0 1 1 0 1 2 0 1 3 0 2 0 0 2 1 0 2 2 0 2 3 0 Basically, the two conditions are that 'A' must be 0, and 'B' must be more than 1. "Mike H" wrote: Hi, Perhaps we could see a small amount of test data and the result you expect. Mike "JS25" wrote: Hi all, I'd really appreciate help with what is, I'm sure, a simple problem that I'm too stupid to solve! The problem - Using COUNTIF (I think), the problem is that it requires two criteria/conditions to be met: For the range to be counted (as 1, NOT 2), A1 must have a value of 0 AND B1 must be more than 1. ONLY if these conditions are meet should the range be counted as 1. Eagerly awaiting for reply, JS25 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif, multiple criteria
Try this:
=--AND(COUNT(A1:B1)=2,A1=0,B11) Copy down as needed. -- Biff Microsoft Excel MVP "JS25" wrote in message ... Certainly: 'A' 'B' Expect Result - 0 0 0 0 1 0 0 2 1 0 3 1 1 0 0 1 1 0 1 2 0 1 3 0 2 0 0 2 1 0 2 2 0 2 3 0 Basically, the two conditions are that 'A' must be 0, and 'B' must be more than 1. "Mike H" wrote: Hi, Perhaps we could see a small amount of test data and the result you expect. Mike "JS25" wrote: Hi all, I'd really appreciate help with what is, I'm sure, a simple problem that I'm too stupid to solve! The problem - Using COUNTIF (I think), the problem is that it requires two criteria/conditions to be met: For the range to be counted (as 1, NOT 2), A1 must have a value of 0 AND B1 must be more than 1. ONLY if these conditions are meet should the range be counted as 1. Eagerly awaiting for reply, JS25 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif, multiple criteria
That is great, thank you so much :D You're a genius, bravo bravo :D
Once again, I thank you! "T. Valko" wrote: Try this: =--AND(COUNT(A1:B1)=2,A1=0,B11) Copy down as needed. -- Biff Microsoft Excel MVP "JS25" wrote in message ... Certainly: 'A' 'B' Expect Result - 0 0 0 0 1 0 0 2 1 0 3 1 1 0 0 1 1 0 1 2 0 1 3 0 2 0 0 2 1 0 2 2 0 2 3 0 Basically, the two conditions are that 'A' must be 0, and 'B' must be more than 1. "Mike H" wrote: Hi, Perhaps we could see a small amount of test data and the result you expect. Mike "JS25" wrote: Hi all, I'd really appreciate help with what is, I'm sure, a simple problem that I'm too stupid to solve! The problem - Using COUNTIF (I think), the problem is that it requires two criteria/conditions to be met: For the range to be counted (as 1, NOT 2), A1 must have a value of 0 AND B1 must be more than 1. ONLY if these conditions are meet should the range be counted as 1. Eagerly awaiting for reply, JS25 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif, multiple criteria
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "JS25" wrote in message ... That is great, thank you so much :D You're a genius, bravo bravo :D Once again, I thank you! "T. Valko" wrote: Try this: =--AND(COUNT(A1:B1)=2,A1=0,B11) Copy down as needed. -- Biff Microsoft Excel MVP "JS25" wrote in message ... Certainly: 'A' 'B' Expect Result - 0 0 0 0 1 0 0 2 1 0 3 1 1 0 0 1 1 0 1 2 0 1 3 0 2 0 0 2 1 0 2 2 0 2 3 0 Basically, the two conditions are that 'A' must be 0, and 'B' must be more than 1. "Mike H" wrote: Hi, Perhaps we could see a small amount of test data and the result you expect. Mike "JS25" wrote: Hi all, I'd really appreciate help with what is, I'm sure, a simple problem that I'm too stupid to solve! The problem - Using COUNTIF (I think), the problem is that it requires two criteria/conditions to be met: For the range to be counted (as 1, NOT 2), A1 must have a value of 0 AND B1 must be more than 1. ONLY if these conditions are meet should the range be counted as 1. Eagerly awaiting for reply, JS25 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif, multiple criteria
Hi,
The formula for your third column would be =--AND(A1=0,B11) for an overall count: =SUMPRODUCT(--(A1:A12=0),--(B1:B121)) or if you are using 2007: =COUNTIFS(A1:A12,0,B1:B12,"1") -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JS25" wrote: Hi all, I'd really appreciate help with what is, I'm sure, a simple problem that I'm too stupid to solve! The problem - Using COUNTIF (I think), the problem is that it requires two criteria/conditions to be met: For the range to be counted (as 1, NOT 2), A1 must have a value of 0 AND B1 must be more than 1. ONLY if these conditions are meet should the range be counted as 1. Eagerly awaiting for reply, JS25 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif with multiple criteria | Excel Worksheet Functions | |||
Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
COUNTIF - Multiple Criteria | Excel Worksheet Functions | |||
Countif for multiple criteria | Excel Worksheet Functions | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |