Excel - chechking corresponding data

R

royend

Hi,

I want to make a calculation based on two corresponding columns o
data. In B7:B500 the cells are blank or filled with an x. In C7:C50
the cells are blank or filled with a number.

I need to count how many rows have a number in C and at the same tim
is blank in B.

What I tryed was:
=SUMPRODUCT(COUNTIF(B7:B500,"=x"),COUNTIF(C7:C500,"<>"""))

It didn't work, so now I truly need some assistance from you.

Thanks in advance.

Ro
 
R

royend

Hi and thanks for your suggestions.

Somehow it didn't work. It seems to be a bit moody today, and I can'
figure out why this ain't working...

The arrays should work though. The three first rows gives this:
(FALSE; TRUE; FASLE) and
(TRUE; TRUE; TRUE)

Which should give me the answer 1. But instead I get 0.

Any other ideas and tips are welcome.

Best Regards,
Ro
 
P

Peo Sjoblom

The arrays should work though. The three first rows gives this:
(FALSE; TRUE; FASLE) and
(TRUE; TRUE; TRUE)

Which should give me the answer 1. But instead I get 0.

Any other ideas and tips are welcome.

Best Regards,
Roy
Click to expand...


Post the exact formula that gives you zero with

{FALSE;TRUE;FALSE} and {TRUE;TRUE;TRUE}

it sounds to me as you have some extra parenthesis in there
 
R

Ragdyer

Two things.

First, did you try *both* formulas?
They're not *exactly* the same!
They will read column C differently.

Which leads to the second question.
How are the columns populated ... keyboard or formulas?

If formulas, try some keyboard entries with X's and numbers, and see what
results you get.
If you start seeing expected returns with keyboard entries, (which is what I
tested with, and expect Trevor did also), examine closely exactly what your
formulas are returning to those columns.

If you're still experiencing problems, post back with your formulas.
 
R

royend

Thanks for great replies!

This seems to give me the correct answer:
=SUMPRODUCT(--(B7:B500="N"),--(C7:C500<100))

I have replaced the blanks with an N, so this formula counts where th
B column equals N and where the C column is less than 100.

Thanks again!

:)

Ro
 
T

Trevor Shuttleworth

Yep: blank or x in column B, blanks and numbers in column C, all keyed in
.... no imagination ;-)


Ragdyer said:
Two things.

First, did you try *both* formulas?
They're not *exactly* the same!
They will read column C differently.

Which leads to the second question.
How are the columns populated ... keyboard or formulas?

If formulas, try some keyboard entries with X's and numbers, and see what
results you get.
If you start seeing expected returns with keyboard entries, (which is what I
tested with, and expect Trevor did also), examine closely exactly what your
formulas are returning to those columns.

If you're still experiencing problems, post back with your formulas.
--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -
Click to expand...
 
Top