Formula help req'd

[Phil]

I am trying to find the formula that will compare 2 columns, i.e B and
C, and show the result in a single cell, i.e J. I can compare single
cells in the columns but do not want to type the formula for every
cell in the columns.

Formula i have now is- =IF(B4>C4,J3+1,J3+0)

Simple maybe but being new to Excel i cant figure it out.

TIA.

 

[Bob Phillips]

If it is the sum you want to compare, you can use

=IF(SUM(B:B)>SUM(C:C),J3+1,J3)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Phil]

thanks but thats not quite it.
What i want is , for instance , if B1>C1 then J3+1
if B2>C2 then J3+1, etc down the whole B and C columns .
So if B > C 5 times out of 10 then J3 would equal 5.
thx

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(B2:B300>C2:C300))

adapt to fit your range will count the occurrences where
B is greater than C

so using J3
which seems to be static

=SUMPRODUCT(--(B2:B300>C2:C300))+J3

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Stunn]

Hi Peo,

I think he needs that first formula to be IN J3.

 

[Peo Sjoblom]

Oops! Close enough <g>

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Phil]

Works, thanks a lot

One way

=SUMPRODUCT(--(B2:B300>C2:C300))

adapt to fit your range will count the occurrences where
B is greater than C

so using J3
which seems to be static

=SUMPRODUCT(--(B2:B300>C2:C300))+J3

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)