count and index issue

[jw01]

hello

having some issues with my formula...i am running a contest and my data is not being captured correctly.

in sheet "CSA Details breakdown" the person input their data (ie. name column D and revenue column L). however, in cell P16 (highlighted red), the row does not activate.

the reason why i need it to show data is b/c the payout in the "rps conest payout" sheet, should show in cell D40 for P10 the person's name and deal.

the issue seems to be in the person name. I use "john doe" in the "csa detail" worksheet, however if i change in cell D16 to "j. doe"...for formula picks it up

formula in P16:
=IF(G16='RPS Contest Payout'!$D$37,IF(COUNTIF($D$14:D16,D16)=1,ROW(),""),"")


can someone pls help as it's causing a major issue, pls and thx u

[NBVC]

here: COUNTIF($D$14:D16,D16)=1 you are saying if the Count from D14 to D16 of value in D16 is 1 then submit Row number... there are 3 John Doe's so it will not equal 1 and therefore it will return the FALSE part (i.e. Null). When you change D16 to J. Doe, then your count of J.Doe is 1 and so you get Row number returned...

[Dave H9]

This part - COUNTIF($D$14:D16,D16)=1 - is counting the number of times "John Doe" appears in range D14:D16, it appears 3 times (which doesn't equal 1) hence the result is "".

Dave H

[Fotis1991]

..Maybe to try this and copy down?

Please Login or Register  to view this content.

[jw01]

Hello Fotis

Thxs for the input however, when i used your formula

=IF(G14='RPS Contest Payout'!$D$37,IF(COUNTIF($D14:D14,D14)=1,ROW(),""),"")

it does function how i want, however...in the "rps contest payout" sheet, it shows the name multiple times, instead of just once...any thoughts? thxs alot.

[jw01]

here: COUNTIF($D$14:D16,D16)=1 you are saying if the Count from D14 to D16 of value in D16 is 1 then submit Row number... there are 3 John Doe's so it will not equal 1 and therefore it will return the FALSE part (i.e. Null). When you change D16 to J. Doe, then your count of J.Doe is 1 and so you get Row number returned...

any suggestions how i can avoid that....yet at the same time on my "rps contest payout" sheet, have the name only appear once? thxs for your input.

[NBVC]

can you use:

COUNTIF($D$14:D16,D16)>=1

[Tsjallie]

If I assume that you count the occurences of a name to check whether it is unique and if I assume that they should also be unique within a period then you should involve the period in the countif.
In that case you could try to alter the formula in P16 to something like:

Please Login or Register  to view this content.

Can be coded shorter, but (given my assumptions) should/could do the trick.

[jw01]

i used the formula

however it appears is now in the "rps contest payout" sheet

cell D19
=IF(ROWS($A$1:A1)>COUNT('CSA detail breakdown'!$O$14:$O$1020),"",INDEX('CSA detail breakdown'!$D$14:$D$1020,MATCH(SMALL('CSA detail breakdown'!$O$14:$O$1020,ROWS($A$1:A1)),'CSA detail breakdown'!$O$14:$O$1020,0)))

it is listing the names twice

i have attached a sample update

any way to work around this? thx u

[jw01]

can you use:

COUNTIF($D$14:D16,D16)>=1

hello nbvc

i tried your approach

however it appears the issue is also in the "rps contest payout" sheet

cell D19
=IF(ROWS($A$1:A1)>COUNT('CSA detail breakdown'!$O$14:$O$1020),"",INDEX('CSA detail breakdown'!$D$14:$D$1020,MATCH(SMALL('CSA detail breakdown'!$O$14:$O$1020,ROWS($A$1:A1)),'CSA detail breakdown'!$O$14:$O$1020,0)))

any thoughts on this? thxs again for your great help.

[jw01]

any help on this guys ? pls and thx u

[NBVC]

Maybe you need COUNTIFS to include the Period Sold in the count...

For P9:

=IF(G14='RPS Contest Payout'!$D$16,IF(COUNTIFS($D$14:D14,D14,$G$14:G14,G14)=1,ROW(),""),"")

copied down

[jw01]

hello NBVC

i tried your approach.

in the "rps payout" worksheet, in cell D40 for P10, the name does not appear - see attached

=IF(ROWS($A$1:A1)>COUNT('CSA detail breakdown'!$P$14:$P$1020),"",INDEX('CSA detail breakdown'!$D$14:$D$1020,MATCH(SMALL('CSA detail breakdown'!$P$14:$P$1020,ROWS($A$1:A1)),'CSA detail breakdown'!$P$14:$P$1020,0)))


also, this is the formula i have in cell P14 copied down for P10 in the "csa detail" worksheet

=IF(G14='RPS Contest Payout'!$D$36,IF(COUNTIFS($D$14:D14,D14,$G$14:G14,G14)=1,ROW(),""),"")

thxs

[NBVC]

P14 should be:

=IF(G14='RPS Contest Payout'!$D$37,IF(COUNTIFS($D$14:D14,D14,$G$14:G14,G14)=1,ROW(),""),"")

you were referencing $D$36... which is blank

[jw01]

omg thx u so, so much!

just real quick....if users have 2007excel, is there a way around countifs?

[NBVC]

COUNTIFS($D$14:D14,D14,$G$14:G14,G14)=1

becomes

SUMPRODUCT(($D$14:D14=D14)*($G$14:G14=G14))=1

[jw01]

thx u so much sir!

[icestationzbra]

jw01 - if i understand you correctly, then:

XL2003 = SUMPRODUCT
XL2007 = COUNTIFS