Hello,
Following up on previous help provided and which as worked great:

Without having to add a column to a spreadsheet, I need to count the number
of rows that match criteria based on three columns. That is, If column A is
blank AND column B=0 AND column C=0, this would count as 1 valid row,
otherwise 0 valid row. It looks like I could do this somehow with IF() and
I'd like to use one of the functions you guys have been referencing (sumIF,
countIF, etc.). I do not know how to use compound criteria. I thought I
could do something like =Sum(IF(A="" AND B=0 AND C=0, 1, 0)), but that
doesn't
seem to work. I see there is an AND function: AND(A="",B=0,C=0) or
something like
that. I know I could use a DCount() function, but I was hoping to avoid
using
criteria columns in my spreadsheet. Very confused.

Thanks for any help!

John

=SUMPRODUCT(--(A1:A100=""),--(B1:B100=0),--(C1:C100=0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"jb" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hello,
> Following up on previous help provided and which as worked great:
>
> Without having to add a column to a spreadsheet, I need to count the
> number
> of rows that match criteria based on three columns. That is, If column A
> is
> blank AND column B=0 AND column C=0, this would count as 1 valid row,
> otherwise 0 valid row. It looks like I could do this somehow with IF()
> and
> I'd like to use one of the functions you guys have been referencing
> (sumIF,
> countIF, etc.). I do not know how to use compound criteria. I thought I
> could do something like =Sum(IF(A="" AND B=0 AND C=0, 1, 0)), but that
> doesn't
> seem to work. I see there is an AND function: AND(A="",B=0,C=0) or
> something like
> that. I know I could use a DCount() function, but I was hoping to avoid
> using
> criteria columns in my spreadsheet. Very confused.
>
> Thanks for any help!
>
> John
>
>

Perfect. Saw that function but had no idea to use it like that, not in 100
years.

Reading the help screen and finding this out on my own, it appears that the
range has to be the same for all arrays (a1:a100, b1:b100, etc.) In the
spreadsheet I am referencing, new data is imported regularly so that the
number of rows will most likely change. Is there a way to determine the
number of rows in that spreadsheet to get the second half of the array
number (that is, I know that their will always be a header in row 1 so the
data will start in row 2. I do not know where it will end A2:A????)

Thanks a lot - not a big deal if this is very complex.

John
"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> =SUMPRODUCT(--(A1:A100=""),--(B1:B100=0),--(C1:C100=0))
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> "jb" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Hello,
>> Following up on previous help provided and which as worked great:
>>
>> Without having to add a column to a spreadsheet, I need to count the
>> number
>> of rows that match criteria based on three columns. That is, If column A
>> is
>> blank AND column B=0 AND column C=0, this would count as 1 valid row,
>> otherwise 0 valid row. It looks like I could do this somehow with IF()
>> and
>> I'd like to use one of the functions you guys have been referencing
>> (sumIF,
>> countIF, etc.). I do not know how to use compound criteria. I thought I
>> could do something like =Sum(IF(A="" AND B=0 AND C=0, 1, 0)), but that
>> doesn't
>> seem to work. I see there is an AND function: AND(A="",B=0,C=0) or
>> something like
>> that. I know I could use a DCount() function, but I was hoping to avoid
>> using
>> criteria columns in my spreadsheet. Very confused.
>>
>> Thanks for any help!
>>
>> John
>>
>>
>
>

jb,

Here is a way to do this in code>
Sub jb()

Dim iLastRow As Long
Dim I As Long
Dim J As Long

iLastRow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row
J = 0
Range("A1").Select

Do Until iLastRow = 0
If ((ActiveCell = "") And (ActiveCell.Offset(0, 1).Value <> "") And
(ActiveCell.Offset(0, 1).Value <> "")) Then
J = J + 1
End If

iLastRow = iLastRow - 1
ActiveCell.Offset(1, 0).Select
Loop

Range("E1") = J 'where ever you want the total to show

End Sub

"jb" wrote:

> Hello,
> Following up on previous help provided and which as worked great:
>
> Without having to add a column to a spreadsheet, I need to count the number
> of rows that match criteria based on three columns. That is, If column A is
> blank AND column B=0 AND column C=0, this would count as 1 valid row,
> otherwise 0 valid row. It looks like I could do this somehow with IF() and
> I'd like to use one of the functions you guys have been referencing (sumIF,
> countIF, etc.). I do not know how to use compound criteria. I thought I
> could do something like =Sum(IF(A="" AND B=0 AND C=0, 1, 0)), but that
> doesn't
> seem to work. I see there is an AND function: AND(A="",B=0,C=0) or
> something like
> that. I know I could use a DCount() function, but I was hoping to avoid
> using
> criteria columns in my spreadsheet. Very confused.
>
> Thanks for any help!
>
> John
>
>
>

Thank you. I'll give that a bid.

John
"JRForm" <(E-Mail Removed)> wrote in message
news:6D35864F-7572-4B99-B271-(E-Mail Removed)...
> jb,
>
> Here is a way to do this in code>
> Sub jb()
>
> Dim iLastRow As Long
> Dim I As Long
> Dim J As Long
>
> iLastRow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row
> J = 0
> Range("A1").Select
>
> Do Until iLastRow = 0
> If ((ActiveCell = "") And (ActiveCell.Offset(0, 1).Value <> "") And
> (ActiveCell.Offset(0, 1).Value <> "")) Then
> J = J + 1
> End If
>
> iLastRow = iLastRow - 1
> ActiveCell.Offset(1, 0).Select
> Loop
>
> Range("E1") = J 'where ever you want the total to show
>
> End Sub
>
> "jb" wrote:
>
>> Hello,
>> Following up on previous help provided and which as worked great:
>>
>> Without having to add a column to a spreadsheet, I need to count the
>> number
>> of rows that match criteria based on three columns. That is, If column A
>> is
>> blank AND column B=0 AND column C=0, this would count as 1 valid row,
>> otherwise 0 valid row. It looks like I could do this somehow with IF()
>> and
>> I'd like to use one of the functions you guys have been referencing
>> (sumIF,
>> countIF, etc.). I do not know how to use compound criteria. I thought I
>> could do something like =Sum(IF(A="" AND B=0 AND C=0, 1, 0)), but that
>> doesn't
>> seem to work. I see there is an AND function: AND(A="",B=0,C=0) or
>> something like
>> that. I know I could use a DCount() function, but I was hoping to avoid
>> using
>> criteria columns in my spreadsheet. Very confused.
>>
>> Thanks for any help!
>>
>> John
>>
>>
>>

Actually, I just realized that the first column of my data will always have
a value so I can just do a =Count(A1:A10000) and it returns me the number of
non-blank rows. Do you know by chance, how I would use that in specifying a
range? That is, if in a formula I have A1:A600 and I want to use the
results of my Count() function instead of the 600, is there a way to do some
concatenation or something to reference the Count function in the range
(A1:A&Count(Spreadsheet2!A1:A10000)) something like that?

"JRForm" <(E-Mail Removed)> wrote in message
news:6D35864F-7572-4B99-B271-(E-Mail Removed)...
> jb,
>
> Here is a way to do this in code>
> Sub jb()
>
> Dim iLastRow As Long
> Dim I As Long
> Dim J As Long
>
> iLastRow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row
> J = 0
> Range("A1").Select
>
> Do Until iLastRow = 0
> If ((ActiveCell = "") And (ActiveCell.Offset(0, 1).Value <> "") And
> (ActiveCell.Offset(0, 1).Value <> "")) Then
> J = J + 1
> End If
>
> iLastRow = iLastRow - 1
> ActiveCell.Offset(1, 0).Select
> Loop
>
> Range("E1") = J 'where ever you want the total to show
>
> End Sub
>
> "jb" wrote:
>
>> Hello,
>> Following up on previous help provided and which as worked great:
>>
>> Without having to add a column to a spreadsheet, I need to count the
>> number
>> of rows that match criteria based on three columns. That is, If column A
>> is
>> blank AND column B=0 AND column C=0, this would count as 1 valid row,
>> otherwise 0 valid row. It looks like I could do this somehow with IF()
>> and
>> I'd like to use one of the functions you guys have been referencing
>> (sumIF,
>> countIF, etc.). I do not know how to use compound criteria. I thought I
>> could do something like =Sum(IF(A="" AND B=0 AND C=0, 1, 0)), but that
>> doesn't
>> seem to work. I see there is an AND function: AND(A="",B=0,C=0) or
>> something like
>> that. I know I could use a DCount() function, but I was hoping to avoid
>> using
>> criteria columns in my spreadsheet. Very confused.
>>
>> Thanks for any help!
>>
>> John
>>
>>
>>

Not too bad

=SUMPRODUCT(--(OFFSET(A2,,,COUNTA(A:A)-1,1)=""),--(OFFSET(B2,,,COUNTA(A:A)-1,1)=0),--(OFFSET(C2,,,COUNTA(A:A)-1,1)=0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"jb" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Perfect. Saw that function but had no idea to use it like that, not in
> 100 years.
>
> Reading the help screen and finding this out on my own, it appears that
> the range has to be the same for all arrays (a1:a100, b1:b100, etc.) In
> the spreadsheet I am referencing, new data is imported regularly so that
> the number of rows will most likely change. Is there a way to determine
> the number of rows in that spreadsheet to get the second half of the array
> number (that is, I know that their will always be a header in row 1 so the
> data will start in row 2. I do not know where it will end A2:A????)
>
> Thanks a lot - not a big deal if this is very complex.
>
> John
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> =SUMPRODUCT(--(A1:A100=""),--(B1:B100=0),--(C1:C100=0))
>>
>> --
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>> "jb" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> Hello,
>>> Following up on previous help provided and which as worked great:
>>>
>>> Without having to add a column to a spreadsheet, I need to count the
>>> number
>>> of rows that match criteria based on three columns. That is, If column
>>> A is
>>> blank AND column B=0 AND column C=0, this would count as 1 valid row,
>>> otherwise 0 valid row. It looks like I could do this somehow with IF()
>>> and
>>> I'd like to use one of the functions you guys have been referencing
>>> (sumIF,
>>> countIF, etc.). I do not know how to use compound criteria. I thought
>>> I
>>> could do something like =Sum(IF(A="" AND B=0 AND C=0, 1, 0)), but that
>>> doesn't
>>> seem to work. I see there is an AND function: AND(A="",B=0,C=0) or
>>> something like
>>> that. I know I could use a DCount() function, but I was hoping to avoid
>>> using
>>> criteria columns in my spreadsheet. Very confused.
>>>
>>> Thanks for any help!
>>>
>>> John
>>>
>>>
>>
>>
>
>

Wow, that's out there! I'll give that a bid. Thanks very much.

John

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:uKWh%(E-Mail Removed)...
> Not too bad
>
> =SUMPRODUCT(--(OFFSET(A2,,,COUNTA(A:A)-1,1)=""),--(OFFSET(B2,,,COUNTA(A:A)-1,1)=0),--(OFFSET(C2,,,COUNTA(A:A)-1,1)=0))
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> "jb" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Perfect. Saw that function but had no idea to use it like that, not in
>> 100 years.
>>
>> Reading the help screen and finding this out on my own, it appears that
>> the range has to be the same for all arrays (a1:a100, b1:b100, etc.) In
>> the spreadsheet I am referencing, new data is imported regularly so that
>> the number of rows will most likely change. Is there a way to determine
>> the number of rows in that spreadsheet to get the second half of the
>> array number (that is, I know that their will always be a header in row 1
>> so the data will start in row 2. I do not know where it will end
>> A2:A????)
>>
>> Thanks a lot - not a big deal if this is very complex.
>>
>> John
>> "Bob Phillips" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> =SUMPRODUCT(--(A1:A100=""),--(B1:B100=0),--(C1:C100=0))
>>>
>>> --
>>> HTH
>>>
>>> Bob
>>>
>>> (there's no email, no snail mail, but somewhere should be gmail in my
>>> addy)
>>>
>>> "jb" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> Hello,
>>>> Following up on previous help provided and which as worked great:
>>>>
>>>> Without having to add a column to a spreadsheet, I need to count the
>>>> number
>>>> of rows that match criteria based on three columns. That is, If column
>>>> A is
>>>> blank AND column B=0 AND column C=0, this would count as 1 valid row,
>>>> otherwise 0 valid row. It looks like I could do this somehow with IF()
>>>> and
>>>> I'd like to use one of the functions you guys have been referencing
>>>> (sumIF,
>>>> countIF, etc.). I do not know how to use compound criteria. I thought
>>>> I
>>>> could do something like =Sum(IF(A="" AND B=0 AND C=0, 1, 0)), but that
>>>> doesn't
>>>> seem to work. I see there is an AND function: AND(A="",B=0,C=0) or
>>>> something like
>>>> that. I know I could use a DCount() function, but I was hoping to
>>>> avoid using
>>>> criteria columns in my spreadsheet. Very confused.
>>>>
>>>> Thanks for any help!
>>>>
>>>> John
>>>>
>>>>
>>>
>>>
>>
>>
>
>