Previous Topic Next Topic

EXCEL - COUNT 2 DIF. VALUES IN 2 DIF. RANGES

Closed
WALRUSSSSSS Posts 1 Registration date Tuesday June 30, 2009 Status Member Last seen July 1, 2009 - Jul 1, 2009 at 10:03 AM
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 - Jul 2, 2009 at 01:06 AM
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.
Related:

2 responses

Answer 1 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jul 1, 2009 at 08:32 PM
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.;
0
Answer 2 / 2
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 166
Jul 2, 2009 at 01:06 AM
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 ........

0