SumIF full columns, multiple conditions

[Guest]

In column A:A, B:B, i have data. I want a formula to count the number of
times the two pieces of data appaer in the same row. For example if A:A
contains
1
2
3 and B:B contains
A
B
C
I would like it to count the number of times B appears next to the number 1.
I need this done for an entire column, and looking through past helps
questions I get the idea that pivot tables would be useful here, however I
dont want to use them. Is there any other solution? (In essence its a SUM if
formula with two conditions)

 

[Bob Phillips]

=SUMPRODUCT(--A1:A65535=1),--(B1:B65535="A"))

Note that array function don't work with complete columns, you have to
specify a range.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[mrice]

You could consider putting a concatentation formula into column C

= A1 & B1 in cell C1

You could then count how many times "B1" appears in column C

 

[Bob Phillips]

Typo

=SUMPRODUCT(--(A1:A65535=1),--(B1:B65535="A"))


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)