Indirect/Substitute in sums

[nrage21]

Hi forum,

Just wanted to get confirmation from someone that might know. When Substitute is used with Indirect in summing ranges does it allow the substitution to take place?

I ran some small scale experiments, but was unsuccessful. I tried googling it, but my search returned no hard evidence.

Regards,
nrage21

[NBVC]

Can you give an example?

[nrage21]

NBVC,

Take a look at the RESULTS sheet; cell C19

I am attempting to sum the ranges found in the Sheets Data, Cricket, and Cricket(2) by substituting the "*", but havent been successful yet.

At least I was able to sum the same ranges but for values that did not had the "*"

This is a project that belongs to all4excel

Regards,
nrage21

[NBVC]

I'm not sure you can do that so easily....

....but I have to depart now...so will think about it tomorrow.

[nrage21]

Thanks buddy

It is a real challenge, specially with the little symbols floating and concatenating symbols. I tried moving Substitute to different positions within the formula and get #value, #ref, 0, and 908... none which satisfy the objective.

I am thinking that perhaps it would be a good idea to construct a completely new worksheet function that does not use Indirect, but we'll see. Part of the problem is that the formula needs to account for when new sheets are added which has been addressed with offset.

I'm going to keep trying

Regards,
nrage21

[all4excel]

Dear Nrage,

Just wanted to inform you In that Cricket-II file the Tabs Cricket and Cricket(2) and A can be ignored they were created when I was trying these codes..

However all the information is present in the Tabs "Data" and "RESULTS"..

The Expectation can give you an ideas to what we are looking for however everything works fine in this file..

The file Dynamic Addition would need to use some logic from here for considering the "*".

So you mauy need to add Dynamic Addition 3.0 over here if you wish to..

as Cricket-II is an example of how it works in one sheet with the Asterik logic..

But the same should be replicated with Different Sheets in the Dynamic Addition file..



all4excel.

[NBVC]

So, I think the best solution would be to add a helper column that extracts the numbers with *. Then you can use the previous Sumproduct(Sumif()) formula and sum this helper column..

The helper column can be hidden... and the formula in it can be copied down as far as you want so it updates with future entries.

See attached....

If only the Data sheet is ever going to be used, then see formula in C24...

[all4excel]

Dear NBVC and Nrage..

This works fine however as per this file where we compare individual cells but when its a Range then how we would we use that?

I'll just attach the file..to give an idea..

In this case we have the data in contiguos rows but the Actual file we have the data not only in Different Sheets but also in different Rows..

All4excel.

[NBVC]

Try this formula in G2, copied down:

Please Login or Register  to view this content.

[all4excel]

This formula worked like a charm..

[ = INT(SUMPRODUCT(SUMIF(INDIRECT("'"&Tabnames&"'!A3:A13"),$B2,INDIRECT("'"&Tabnames&"'!I3:I13"))*10-INT(SUMIF(INDIRECT("'"&Tabnames&"'!A3:A13"),$B2,INDIRECT("'"&Tabnames&"'!I3:I13")))*4)/6)+MOD(SUMPRODUCT(SUMIF(INDIRECT("'"&Tabnames&"'!A3:A13"),$B2,INDIRECT("'"&Tabnames&"'!I3:I13"))*10-INT(SUMIF(INDIRECT("'"&Tabnames&"'!A3:A13"),$B2,INDIRECT("'"&Tabnames&"'!I3:I13")))*4),6)/10 ]

Can something similar be done for the Total Runs as that is working fine without the "*".?

all4excel

[NBVC]

You mean:

Please Login or Register  to view this content.

in C2, copied down

[all4excel]

Things looking to achieve already shown in Cricket-II file as well as somethings from Dynamic Addition file..

BATTING :
Total Runs ---Total Runs including numbers with and without asterik ( * )

Not Out--Only counting the instances when the Batsman was "Not-out".

Average--Total Runs / ( Total Matches played - Not Out instances )

Highest--Highest score - there are 2 instances of 186 and 186*, then the one with [ * ] would be considered.

Lowest--Lowest score - if there are two instances of 48 and 48*, then the one without [ * ] would be considered.

BOWLING :

This is a new addition which was not considered earlier.
The main crux is to be able to derive the MAX and MIN from different sheets and different Rows.

Best bowling Figures--This would be based on the Number of wickets and the number of runs and should be displayed as 3-36 which means that the bowler took 3 wickets at the cost of 36 runs in a match..

But if there are more than one occasion of taking the same number of wickets then the runs conceded should be given weightage..

Example :
1. 3-39
2 3-26

Naturally 3-26 should be the Best bowling figures.

Again if the runs are same then the number of wickets would beconsidered in the Best bowling figures.

1. 3-36
2. 1-36

Naturally 3-36 should be the Best bowling figures.

all4excel.

[NBVC]

Please post separate questions in separate threads...