Counting two criteria

[dips_007]

Hi all i need help i have 2 columns in excel and i want to be able to do a count on. eg i want to find a A's in first column then with that information find out which rows have B in the second column

Column 1 Column 2
a s
a f
a b
s d
c l
a b
v v


{=SUM(IF(Sheet2!B:B="A",IF(Sheet2!R:R="B",1,0)))}

Total should be 3 but i need to get this to work on MS Excel 2000 or 2003. It works fine on 2007

[VBA Noob]

I make it two

Please Login or Register  to view this content.

VBA Noob

[dips_007]

I make it two

Please Login or Register  to view this content.

VBA Noob

sorry typo

[dips_007]

Can it be done for a whole column as the data is from another source and it will change, [lis the information is from another sheet

I take it, its some thing like below. Can you explain to me whats happening please as i dont understand the formula

=SUMPRODUCT(--(Sheet2!B:B="a")*(Sheet2!R:R="b"))

[VBA Noob]

Not a whole column. Down to cell 65535 (last cell must be blank)

=SUMPRODUCT(--(Sheet2!A1:A7="a")*(Sheet2!B1:B7="b"))

VBA Noob

[dips_007]

Not a whole column. Down to cell 65535 (last cell must be blank)



VBA Noob

Can you explain to me whats happening please as i dont understand the formula

[VBA Noob]

This parts checks that the range = a on sheet 2 ...change to your sheet

Sheet2!A1:A7="a"

and this part checks that the range = b on sheet 2 ...change to your sheet

Sheet2!B1:B7="b"

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

VBA Noob

[dips_007]

This parts checks that the range = a on sheet 2 ...change to your sheet



and this part checks that the range = b on sheet 2 ...change to your sheet




http://www.xldynamic.com/source/xld.SUMPRODUCT.html

VBA Noob

I mean the -- and the *

[Teethless mama]

Hi all i need help i have 2 columns in excel and i want to be able to do a count on. eg i want to find a A's in first column then with that information find out which rows have B in the second column

Column 1 Column 2
a s
a f
a b
s d
c l
a b
v v


{=SUM(IF(Sheet2!B:B="A",IF(Sheet2!R:R="B",1,0)))}

Total should be 3 but i need to get this to work on MS Excel 2000 or 2003. It works fine on 2007

You can not use whole columns if you have older versions of Excel
use B1:B65535 and R1:R65535 instead of B:B and R:R

[dips_007]

HI thanks for that guys how would i say
1) NOT equal to b
2)and also to ignore blanks

[VBA Noob]

<>b

and

Range <> ""

VBA Noob