countif/criteria

Guys,need help with a task for countif.the formula i have here on a data worksheet runs over 7 days each day on a seperate work sheet.what i need to do is be able to change the aylesford to say stora without manually typing each one in time consuming would you say i have abot 12 differrent names to insert. can anybody help.if i try to change it just gives me saturday

cheers

=COUNTIF(Sunday!$C$23,"Aylesford")+COUNTIF(Monday!$C$23,"Aylesford")+COUNTIF(Tuesday!$C$23,"Aylesford")+COUNTIF(Wednesday!$C$23,"Aylesford")+COUNTIF(Thursday!$C$23,"Aylesford")+COUNTIF(Friday!$C$23,"Aylesford")+COUNTIF(Saturday!$C$23,"Aylesford")

A COUNTIF criteria value need not be a constant, that is a hard-wired value - it can be a cell reference - and thus the cell reference can adapt as the formula is copied across cells.

If for ex. we look at Data!B3 we have:

=COUNTIF(Sunday!$C$21,"Aylesford")+COUNTIF(Monday!$C$21,"Aylesford")+COUNTIF(Tuesday!$C$21,"Aylesford")...etc

but this could be re-written based on the fact that B1 contains Aylesford, ie:

=COUNTIF(Sunday!$C$21,B$1)+COUNTIF(Monday!$C$21,B$1)+COUNTIF(Tuesday!$C$21,B$1)...etc

for C3 which references K

=COUNTIF(Sunday!$K$21,B$1)+COUNTIF(Monday!$K$21,B$1)+COUNTIF(Tuesday!$K$21,B$1)...etc

Given each row is referencing a different cell (and there is no even pattern between them) you will need B in each row separately before applying across the remaining columns.

Before others suggest the same - you could use INDIRECT but this would make things more complex and Volatile so better IMO to setup as above.

As for embedding all the COUNTIFs - yes, unfortunately XL does not permit 3D conditional summation, requiring Volatile Sumproduct/Arrays instead and these are not good performers... and so in the face of that I think the approach you have adopted is not a bad one.

With truly high respects to DonkeyOte I have interpreted it a little bit different and came up with folowing solution:

=SUMPRODUCT((COUNTIF(INDIRECT("'"&mysheets&"'!$C$23"),"Aylesford")))

where mysheets is defined as

={"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"}

Cheers

I've made the complete wb a little bit more dynamic.

Please take a look.

NOTE: I see you have numerous merged cells. Try to avoid these.

We are here "ter leeringh ende vermaek". It's Dutch and means that we are here to learn and have fun doing it.

If you have a specific question please ask (maybe new topic as an answer is expected to come much faster). Try to understand what we have done as a lot of this stuff is reoccuring in this forum.