COUNTIF ON 2 VARIABLES ??
 

Thanks for all the previous assistance... however I have now encountered
another couple of problems

Again back to my large spreadsheet issues ... I have several colums of which
I would like to count the reoccurence of a certain value

1 column comprises of a potentially random digit between 1 and 400 ... the
other being either 1 or 0 ...

I am looking for a formula that incrementally counts every time both values
match... thus 345..1 / 232..1 / 345..1 etc would return a count of 2 for 345,
a count of 1 for 232 and 0 for every other value inbetween... I assume the
vb code would be something like
If A = 345 and B = 1 then Count

However can get my head round a suitable formula

Many thanks

Alan

Assumning your data in columns a & b for 27 rows

=SUMPRODUCT((A1:A27=A1)*(B1:B27=B1)*1)
Lance

"Alan" wrote:

Thanks for all the previous assistance... however I have now encountered
another couple of problems

Again back to my large spreadsheet issues ... I have several colums of which
I would like to count the reoccurence of a certain value

1 column comprises of a potentially random digit between 1 and 400 ... the
other being either 1 or 0 ...

I am looking for a formula that incrementally counts every time both values
match... thus 345..1 / 232..1 / 345..1 etc would return a count of 2 for 345,
a count of 1 for 232 and 0 for every other value inbetween... I assume the
vb code would be something like
If A = 345 and B = 1 then Count

However can get my head round a suitable formula

Many thanks

Alan

Thanks Lance

However that appears to count all the appearances of the data value in
column A not just those that have a 1 in column B ... I was looking for a
method to total the number of instances of a value in column A where column B
of the same row was 1 ... however no count is made if column b = 0



"LanceB" wrote:

Assumning your data in columns a & b for 27 rows

=SUMPRODUCT((A1:A27=A1)*(B1:B27=B1)*1)
Lance

"Alan" wrote:

Thanks for all the previous assistance... however I have now encountered
another couple of problems

Again back to my large spreadsheet issues ... I have several colums of which
I would like to count the reoccurence of a certain value

1 column comprises of a potentially random digit between 1 and 400 ... the
other being either 1 or 0 ...

I am looking for a formula that incrementally counts every time both values
match... thus 345..1 / 232..1 / 345..1 etc would return a count of 2 for 345,
a count of 1 for 232 and 0 for every other value inbetween... I assume the
vb code would be something like
If A = 345 and B = 1 then Count

However can get my head round a suitable formula

Many thanks

Alan

Sorry

just change the (B1:B27=B1) to (B1:B27=1)

Lance
"Alan" wrote:

Thanks Lance

However that appears to count all the appearances of the data value in
column A not just those that have a 1 in column B ... I was looking for a
method to total the number of instances of a value in column A where column B
of the same row was 1 ... however no count is made if column b = 0



"LanceB" wrote:

Assumning your data in columns a & b for 27 rows

=SUMPRODUCT((A1:A27=A1)*(B1:B27=B1)*1)
Lance

"Alan" wrote:

Thanks for all the previous assistance... however I have now encountered
another couple of problems

Again back to my large spreadsheet issues ... I have several colums of which
I would like to count the reoccurence of a certain value

1 column comprises of a potentially random digit between 1 and 400 ... the
other being either 1 or 0 ...

I am looking for a formula that incrementally counts every time both values
match... thus 345..1 / 232..1 / 345..1 etc would return a count of 2 for 345,
a count of 1 for 232 and 0 for every other value inbetween... I assume the
vb code would be something like
If A = 345 and B = 1 then Count

However can get my head round a suitable formula

Many thanks

Alan

=SUMPRODUCT(-($A$1:$A$27=$A1),--($B$1:$B$27=1))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alan" wrote in message
...
Thanks Lance

However that appears to count all the appearances of the data value in
column A not just those that have a 1 in column B ... I was looking for a
method to total the number of instances of a value in column A where
column B
of the same row was 1 ... however no count is made if column b = 0



"LanceB" wrote:

Assumning your data in columns a & b for 27 rows

=SUMPRODUCT((A1:A27=A1)*(B1:B27=B1)*1)
Lance

"Alan" wrote:

Thanks for all the previous assistance... however I have now
encountered
another couple of problems

Again back to my large spreadsheet issues ... I have several colums of
which
I would like to count the reoccurence of a certain value

1 column comprises of a potentially random digit between 1 and 400 ...
the
other being either 1 or 0 ...

I am looking for a formula that incrementally counts every time both
values
match... thus 345..1 / 232..1 / 345..1 etc would return a count of 2
for 345,
a count of 1 for 232 and 0 for every other value inbetween... I
assume the
vb code would be something like
If A = 345 and B = 1 then Count

However can get my head round a suitable formula

Many thanks

Alan