Excel - chechking corresponding data

[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

 

[Ragdyer]

Try this:

=SUMPRODUCT((C7:C500<>0)*(B7:B500=""))

 

[Trevor Shuttleworth]

Roy

=SUMPRODUCT((B7:B500="")*(C7:C500<>""))

Regards

Trevor

 

[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

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(ISNUMBER(C7:C500)*(B7:B500=""))

 

[Bernard Liengme]

How about =SUMPRODUCT(--(B7:B500="x"),--NOT(ISBLANK(C&:C500)))

 

[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

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

 

[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.

 

[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

 

[Trevor Shuttleworth]

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

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 !
-------------------------------------------------------------------------- -