Excluding Columns in this Formula

[Gunjani]

I am using the formula =SUMPRODUCT(('Sheet1'!$A$26:$A$147=' Sheet2'!
$A41)*('Sheet1'!$B$26:$BE$147=' Sheet2'!AW$27)) to create a Summary
Table.

Sheet1 contains tables with row of headers from B26:BE147 for each day
of the week (M,T,W,T,F,S,S)continuously starting at row A24, continuing
on A46,A66,A87,A108 concluding for the whole year.

Following on from the above formula I wish to do similar summary table
but only for M-F, separate one for Sat and a separate one for Sun. I am
aware currently my Sat & Sun are abbreviated the same i.e. S, I will
have to arrange to distingush between the two e.g rename.

How may I exclude the respective columns in the formula e.g For a M-F
summary table I would need to exclude all columns labelled S which are
fortunately in sync in Columns B,C,I,J,P,Q,W,X

Incidently the row below each day (A25,A47,A67,A88,A109) is date of the
month i.e 1,2,3,4,,5,etc


--
Many Thanks

Gunjani
What do you do when the only person who can
make you stop crying is the person who made you
cry?

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(('Sheet1'!$A$26:$A$147=' Sheet2'!
$A41)*('Sheet1'!$B$26:$BE$147='
Sheet2'!AW$27)*('Sheet1'!$B$26:$BE$26<>"S"))

 

[Gunjani]

Hi
try
=SUMPRODUCT(('Sheet1'!$A$26:$A$147=' Sheet2'!
$A41)*('Sheet1'!$B$26:$BE$147='
Sheet2'!AW$27)*('Sheet1'!$B$26:$BE$26<>"S"))

Just tried the above formula but does not seem to work .... I think
formula only does table with headers in $B$26:$BE$26, but what about the
the headers in B46:BE46,B66:BE66,B86:BE86 and B106:BE106.



--
Many Thanks

Gunjani
Destiny is not a matter of chance; but a matter of choice.
It is not a thing to be waited for. It is a thing to be
achieved.
-- William Jennings Bryant

 

[Frank Kabel]

Hi
try using separate SUMPRODUCT formulas for each block of data (thats is
separate the blocks with different headers)

 

[Gunjani]

Hi
try using separate SUMPRODUCT formulas for each block of data (thats is
separate the blocks with different headers)

OK thanks, but in the above formula instead of excluding Column headed
S, I wish to exclude the other Columns ( headed M,T,W,T,F) from the
formula i.e A summary sheet for the S column
PLEASE