COUNTIF ON 2 VARIABLES ??

[Guest]

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

 

[Guest]

Assumning your data in columns a & b for 27 rows

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

 

[Guest]

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

 

[Guest]

Sorry

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

Lance

 

[Bob Phillips]

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


--

HTH

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