Inserting a cell broke my function, please help

[Longshot9]

Hi,

I have a spreadsheet that is for a contest for a music festival. Basically what it does is take peoples guesses for the next festival as to what they think the roster will be for it... tallies up points based on comparisons to their guesses and the actual roster, and comes up with a winner. I had this working perfectly last year. It's a two night festival, 6 bands each night. However, when I started preparing it for this year, which is 7 bands the first night, and 6 the second night... inserting the new cell for the 7th band, broke my function to do one of the calculations. The function updated itself when I inserted the new cell for the correct cell range, but it still says #N/A in the cell. I don't know what's wrong.

This specific function is to award people 2 points if they guess the correct night a band plays on. Here's the info on where the data is stored...

Cells C5-C11, Friday nights bands... 1-7 (opener to headliner)
Cells C13-18, Saturday nights bands... 1-6 (opener to headliner)

The first guesses for this years roster went into the following cells...

J27-J33 for Friday night
J35-J40 for saturday night

Cell # G3 is where the calculation is, and should return 2 points for each cell on a specific night that matches a correct band. Doesn't matter if the slot # of the band is right, just as long as it appears on the correct night. So if the user gets 3 bands right on one night, it returns 6 points.

The calculation is this:

=SUMPRODUCT((ISNUMBER(MATCH(J35:J40,C13:C18,0))+ISNUMBER(MATCH(J27:J33,C5:C11,0)))*2)

A user on this site helped me with that last year, i don't recall who it was. On last years spreadsheet, which worked just fine, it was this...

=SUMPRODUCT((ISNUMBER(MATCH(J34:J39,C12:C17,0))+ISNUMBER(MATCH(J27:J32,C5:C10,0)))*2)

Only adjustments made were too the cell # range that changed due to the extra band this year.

The C cells will never change. The "J" cells will change but only the letter. The next user that guesses, his guesses will be in the L column.

Hopefully this is just a simple needing an extra pair of eyes to catch something i'm missing, but I can't see any reason why just changing the cell #s to reflect the new range wouldn't work. Can anyone help?

Thanks!!

 

[DonkeyOte]

Your issue is that your two arrays are now of differing dimensions (one being 7 x 1 and the other 6 x 1) - when you try to add the two together you get an error as there is no Sat item for the 7th result of Fri.

You could simply split the calculation into two separate functions and aggregate else revert to an Array, eg:

=SUM(ISNUMBER(MATCH(J27:J34,C5:C11,0))*2,ISNUMBER(MATCH(J35:J40,C13:C18,0))*2)
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)

 

[Longshot9]

Hi, thanks for the response. Unfortunately I put that in and am now getting 0 returned. I put in the first users guesses, and then put in the exact same bands in for the actual roster... as if he got them all right... and it returned 0 instead of 26 (2 points for each band on friday, 2 for each on saturday).

 

[DonkeyOte]

Presumably you confirmed as an Array as advised ?

If the Array has been set correctly the formula will appear encased within { }
note: these can not be added manually

 

[Longshot9]

Apologies. I didn't quite understand the ctrl+shift+enter part and where to do that. I did it in the cell first and it didn't work. I got it. Thanks a lot