= SUMPRODUCT(--(DAY(Sheet1!$F$2:$F$65536)=DAY(H$1)), --(Sheet1!$R$2:$R$65536=$C2), Sheet1!$AF$2:$AF$65536)
+ SUMPRODUCT(--(DAY(Sheet2!$F$2:$F$65536)=DAY(H$1)), --(Sheet2!$R$2:$R$65536=$C2), Sheet2!$AF$2:$AF$65536)
The first part matches the day in H1 against column F in Sheet1
The second part matches the name in C2 against column R in Sheet 1
The last part adds the scores for the above 2 conditions.
Then repeat for Sheet2
The problems are: 1) The names I am checking against can also be in columns T and V, and 2) the scores to be returned can also be in columns AG and AH.
Ultimately, I?d like to end up with something like this:
If the date in Sheet1 and Sheet 2 column F matches the date in H1
And the name in Sheet1 and Sheet2 columns R,T,V matches the name in C2
Add the scores in Sheet1 and Sheet2 columns AF,AG,AH
Can I use name ranges for dates and names to prevent some ugly formula? I have been unsuccessful in my attempts so far.