EXCEL - COUNT 2 DIF. VALUES IN 2 DIF. RANGES [Closed]

Report
Posts
1
Registration date
Tuesday June 30, 2009
Status
Member
Last seen
July 1, 2009
-
mubashir aziz
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
-
Hello,
i NEED HELP! i HAVE TWO DIFFERENT RANGES, C:C AND E:E. VALUES IN C:C ARE O5,O4, O3, O2, O1, E9,E8,ETC. VALUES IN E:E ARE CC0, AG9,AG8, AG7, ETC. i NEED TO BE ABLE TO COUNT HOW MANY OF THE O5S FROM RANGE C:C ARE ALSO CC0 IN RANGE E:E. i HAVE TRIED SEVERAL THINGS, BUT I DON'T UNDERSTAND WHAT I AM TRYING SO I DON'T KNOW WHY THEY DON'T WORK. I HAVE TRIED, FOR EXAMPLE, =SUMPRODUCT((C:C, "O5")*(E:E, "CC0")) BUT ALL I GET IS THIS: #NUM! WHEN I EVALUATE THE FORMULA, IT DOES NOT SEEM TO RECOGNIZE THE "O5" OR THE "CC0". IT APPEARS THAT SUM FUNCTIONS ARE FOR NUMBERS ONLY, BUT I AM NOT SURE. I HAVE TRIED SEVERAL COUNTIF FORMULAS BUT NONE OF THEM WORK. CAN ANYONE HELP? THANK YOU.

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
I think that in sumproducts there should not be any blanks in the range you are considering.

suppose your data are from c1 to c12 and e1 to e12 only then use this formula


=SUMPRODUCT(($C$1:$C$12="o5")*($E$1:$E$12="cco"))

I have taken alphabet o and 5 and similarly cc and alphabet o;. modify the formula if necessary.;
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
Your formula will work well in MS Excel 2007 as it can work with C:C whole column
=SUMPRODUCT((C:C, "O5")*(E:E, "CC0"))

But in 2003 or below you have to give range C1:C5000 like this ........