SumIF full columns, multiple conditions
 

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)

SumIF full columns, multiple conditions
 

=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)

"Gupta A." wrote in message
...
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)

SumIF full columns, multiple conditions
 

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


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=544850

SumIF full columns, multiple conditions
 

Typo

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


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Bob Phillips" wrote in message
...
=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)

"Gupta A." wrote in message
...
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)