• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Range of cells for use in SUMPRODUCT formula

Grumpy88

Grumpy88

Member
Hi.

I have a current SUMPRODUCT formula in a spreadsheet that is working adequately, but due to also being nested in a series of IF formulas, it is cumbersome and not very flexible. I now wish to amend it to incorporate new selection criteria, but have run into issues with the number of characters contained in the formula (which should tell you something about how long and complex it already is!)

My issue is therefore to find a way of shortening the formula while obviously also retaining all of its functionality. The only area where I can see that shortening is possible though, is a range of 20 cells (two rows of ten columns each - cells G1 to P2) that are addressed in the SUMPRODUCT formula. This range exists for optional user entries, such that any number may be completed as part of the criteria that the SUMPRODUCT formula must take into account, but some cells in that range will probably remain blank (not all 20 need be used).

So much for the background. My SUMPRODUCT formula currently addresses each of those 20 cells individually, which takes up a lot of characters. See the following extract: --((Matches[Season]=$G$1)+(Matches[Season]=$H$1)+(Matches[Season]=$I$1)+(Matches[Season]=$J$1)+(Matches[Season]=$K$1)+(Matches[Season]=$L$1)+(Matches[Season]=$M$1)+(Matches[Season]=$N$1)+(Matches[Season]=$O$1)+(Matches[Season]=$P$1)+(Matches[Season]=$G$2)+(Matches[Season]=$H$2)+(Matches[Season]=$I$2)+(Matches[Season]=$J$2)+(Matches[Season]=$K$2)+(Matches[Season]=$L$2)+(Matches[Season]=$M$2)+(Matches[Season]=$N$2)+(Matches[Season]=$O$2)+(Matches[Season]=$P$2))

Is there a way to shorten this effectively and save on characters that I can use to incorporate the new selection criteria that I want to add? I have tried --((Matches[Season]=$G$1:$P$2)), but it doesn't seem to produce reliable results - I start getting #N/A formula errors. Does anyone have any advice?

Thanks!
 
Hi Grumpy,

If you can post a small sample of your data and criteria instead of the your formula bit, it will be really useful for those who wants to help you out here.

Regards,
 
Thanks, but not sure how practical that is?

It draws on data from several different worksheets, so putting something together for uploading that will be functional could be challenging!
 
@Grumpy88

Just put values in different range. Need not to put formulas for extracting values.

I Suppose you had a table named Matches and Season is a column of that table. Than create that dummy table by putting the values only. G1:p2 suppose criteria. Put that way.

Regards,
 
Another way may be to sumproduct all the values and then subtract what doesn't match, rather than adding up all the matching values
or to use Advanced Filter or Data Base functions instead

Posting the file as is highlighting your formula will be fine as the linked cells willl be available to us as values
 
Will the attached spreadsheet help?

Essentially cell B1 is one of the selection criteria that the SUMPRODUCT formula must take into account (I'd like to add more now), and cells $G$1:$P$2 are another set of user manually-entered criteria that must be taken into account. Any of those season names in the orange cells can be deleted to modify which data from the underlying tables are addressed by SUMPRODUCT and which are ignored ("Season" is indeed a column in the "Matches" worksheet, one of several worksheets that the SUMPRODUCT draws upon to display summarised results in one place).

Thanks.
 

Attachments

  • Book1.xlsx
    8.6 KB · Views: 4
I knew this was going to get complicated to explain!

Essentially the SUMPRODUCT formula (actually a whole bunch of slightly different ones) is in another cell. It would then consider the competition name selected in B1, the season(s) applicable in G1:p2, and a few other things as well. It would then look through the data tables residing in other worksheets (like "Matches" and "Individual Performances" to mention just two) select only data from them that match the competition name, season(s) selected, etc. It would then return the SUM or COUNT or MAX or whatever of those matching rows and columns.

For example, if I wanted to know how many Two-Day matches a given player had appeared in during the 1997/98 and 1998/99 seasons, I would select "Two-Day" at B1 and leave just G1 and H1 completed (deleting the other cell contents in the G1:p2 range) - the player name is selected elsewhere. The SUMPRODUCT formula would then look through thye "Matches" table, count the number of separate matches in which that player appeared that have "Two-Day" indicated in the Competition column and either "1997/98" or "1998/99" indicated in the Season column. It would then return a result of "7" or whatever.

Do I need to simulate the Matches table as well?
 
Actually, they say that a picture is worth a thousand words, so I've attached a screenshot of how the full setup looks in practice.

The columns of data next to each player's name are mostly all SUMPRODUCT formulas drawing different things from underlying tables on other worksheets. The only user interaction thus required in to make selections in B1 and G1 : P2, and everything updates accordingly.
 

Attachments

  • 2014-08-04_10-10-29.png
    2014-08-04_10-10-29.png
    42.4 KB · Views: 7
Hi Graeme ,

I am not sure I have understood your problem , but can you look at the attached file and see whether it applies to your problem.

Narayan
 

Attachments

  • Book3.xlsx
    9 KB · Views: 4
Hi.

Thanks, that is more or less what I'm looking for - a shortened formula that searches through a table and finds entries that match specified criteria. I'm not familiar with TRANSPOSE, but will give converting your formula into what I need for my purposes a shot!
 
Thanks Somendra. Will give your approach a go as well!

Both your and Naran's formulas will take some mental gymnastics on my part in seeing how I can adapt them, but they both seem much more elegant than my ham-fisted approach and are certainly shorter - which is the chief purpose of my post in the first place!
 
Out of curiosity though:

Am I correct in stating that "--((Matches[Season]=$G$1)+(Matches[Season]=$H$1)+(Matches[Season]=$I$1)+(Matches[Season]=$J$1)+(Matches[Season]=$K$1)+(Matches[Season]=$L$1)+(Matches[Season]=$M$1)+(Matches[Season]=$N$1)+(Matches[Season]=$O$1)+(Matches[Season]=$P$1)+(Matches[Season]=$G$2)+(Matches[Season]=$H$2)+(Matches[Season]=$I$2)+(Matches[Season]=$J$2)+(Matches[Season]=$K$2)+(Matches[Season]=$L$2)+(Matches[Season]=$M$2)+(Matches[Season]=$N$2)+(Matches[Season]=$O$2)+(Matches[Season]=$P$2))" and "--((Matches[Season]=$G$1:$P$2))" are not the same thing?

Why would that be?
 
No they are not same. With the longer one you are comparing each row of season with individual cell, so an array of No. of row x 1 is generated, where as with the shorter formula only first two rows are being compared only rest rows will return an error.

Regards,
 
Okay, thanks.

Then I'm not going to make progress with Naranyan's solution (haven't tried yours yet). I've looked up Excel help on TRANSPOSE, and besides converting a vertical cell range to a horizontal one, it also requires that the array range has the same number of columns as the source range. That can't work for my seasons, because they are laid out in a 2 x 10 essentially horizontal range, whereas the Season column in Matches that it must compare to is a single vertical column. That means that TRANSPOSE (x:z) will not work as a shorter substitute for my long-winded (season x)+(season y)+(season z) approach.

Will try your version now.
 
Hi Somendra.

I am extremely happy to announce that your COUNTIF solution seems to be the answer to my issue of reducing the formula sizes by cutting down on the Season components. I have been able to adapt and apply it to my setup, and it seems to be working a treat - I can now add additional selection criteria and still end up with a formula smaller in size than my original was.

Thanks so much (and you too, Naranyank991)!
 
You must log in or register to reply here.
Back
Top