SUMPRODUCT or SUMTHING ELSE???

[MikeA57]

I have a worksheet that is used to score monthly target shooting for a club. Months are the columns and members names are the rows. They want to sum the top 8 scores for each member and use that to select the top 3 shooters for the year. The big problem is that not all contestants show up each month, so some of the guys don't have 8 months of scores. I tried this formula:

=SUMPRODUCT(LARGE(C7:N7,{1,2,3,4,5,6,7,8}))

which works great IF there are 8 scores in the range. If not, I get the dreaded #NUM error message.

I then thought that using an IF statement and COUNTIF to determine if there were 8 scores across all months or not but then what? If there are 8, that settles the TRUE portion of the formula and if there are only 7 scores I could set up the ELSE part of the formula to evaluate 7, but what if there aren't even 7 months with scores? It seems like the SUMPRODUCT function won't work for this...

Does anyone have any ideas about how to determine how many scores there are in each row for the year, sum the top 8 scores if they have them, or sum less if they didn't make it to all the shoots?

Am I just overthinking this?

Thanks guys!

Mike

[NeedForExcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Khalidngo]

Hi Mike,

Have you tried adding a zero with your range? like this:

=SUMPRODUCT(LARGE(C7:N7+0,{1,2,3,4,5,6,7,8}))

It basically converts #NUM! to 0.
#NUM! can not be calculated but the 0.

Blessing

[samba_ravi]

=IF(COUNT(C7:N7)>7,SUMPRODUCT(LARGE(C7:N7,{1,2,3,4,5,6,7,8})),"")
try this

[Tony Valko]

If I understand what you want...

Data Range

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
7	442		48	60	45	32	34	94	25	20	11	63	66	18
8	194		98			13			83
9	415		84	92	40	83	46	43	27
10	-----	-----	-----	-----	-----	-----	-----	-----	-----	-----	-----	-----	-----	-----

This formula entered in A7 and copied down:

=IF(COUNT(C7:N7),SUMPRODUCT(LARGE(C7:N7,ROW(INDIRECT("1:"&MIN(COUNT(C7:N7),8))))),"")

[MikeA57]

That worked perfectly Tony!

Now, the next dilemma occurs because they want to know who actually showed up for at least 8 matches. So, I used a COUNTIF formula as a Conditional Formula to indicate which contestants have scores in 8 or more months. Like this:

=COUNTIF(C3:O3,">=8")

It seemed so simple... But now when I put that conditional formula in each of the score's rows, it reformats every row to Bold/Italic and colored green regardless of whether they are >8 or not.

(I also put the same COUNTIF formula in the R column to show how many matches they showed up for just to check the Conditional Formatted column.)

I've attached a sample of the data to this post.

I really appreciate everyone's suggestions and help. I think this is the last modification that he needs. (But I've been wrong about that before...)

[Tony Valko]

This formula:

=COUNTIF(C3:O3,">=8")

Is counting how many numbers (scores) in the range are greater than or equal to 8.

Because the sores are all over 100 the formula counts every cell that has a value.

I'm thinking that you want to use a COUNT formula:

=COUNT(C3:O3)

This will return the count of numbers (scores) in the range.

Not sure how you want to use this in conjunction with conditional formatting.

[MikeA57]

They only want to issue awards to those who showed up for 8 or more matches. I was simply trying to show that by setting those that fit that criteria with a Bold/Italics/Green score. It seems like the CountIf function should work. Don't know why it isn't...

[Tony Valko]

OK, for conditional formatting, use this formula:

=COUNT(C3:O3)>=8

[MikeA57]

That worked. I kept wondering if I needed to use the COUNTIF with the 2 sections in it or not.

Thank you!!!!!!

I'll get this to him.

Your time is so appreciated Tony.

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.