simplify formula

[Miguelluis]

Hi
I have the formulas below to look at 3 diferent sheets and count values, is there a way to simplify this and perhaps make the sheet a bit faster?
Cell D =SUMPRODUCT(COUNTIFS(Mel!$E$2:$E$400,C8,Mel!$J$2:$J$400,$B$1))+(COUNTIFS(Lisa!$E$2:$E$400,C8,Lisa!$J$2:$J$400,$B$1))+(COUNTIFS(Emma!$E$2:$E$400,C8,Emma!$J$2:$J$400,$B$1))+(COUNTIFS(Charlotte!$E$2:$E$400,C8,Charlotte!$J$2:$J$400,$B$1))+(COUNTIFS(TBC!$E$2:$E$400,C8,TBC!$J$2:$J$400,$B$1))+(COUNTIFS('TBC2'!$E$2:$E$400,C8,'TBC2'!$J$2:$J$400,$B$1))

Cell E =SUMPRODUCT(COUNTIFS(Mel!$E$2:$E$400,C8,Mel!$J$2:$J$400,$B$2))+(COUNTIFS(Lisa!$E$2:$E$400,C8,Lisa!$J$2:$J$400,$B$2))+(COUNTIFS(Emma!$E$2:$E$400,C8,Emma!$J$2:$J$400,$B$2))+(COUNTIFS(Charlotte!$E$2:$E$400,C8,Charlotte!$J$2:$J$400,$B$2))+(COUNTIFS(TBC!$E$2:$E$400,C8,TBC!$J$2:$J$400,$B$2))+(COUNTIFS('TBC2'!$E$2:$E$400,C8,'TBC2'!$J$2:$J$400,$B$2))

Thank you

 

[Gerald Higgins]

I have the formulas below to look at 3 diferent sheets...

They actually seem to be looking at 6 different sheets, so if you really only want them to look at 3 sheets, you could simplify the formulas greatly by deleting 3 of the sheet references.

Also, do you really need this to be a SUMPRODUCT ?
It seems as if you could perhaps just make this a sum of the individual COUNTIFS, which can be setup like this
=COUNTIFS[1]+COUNTIFS[2]+.....

 

[ps1]

Why not use a pivot table to consolidate all the data?

 

[Miguelluis]

Thanks guys, I'll remove the sumproduct see if it makes any diference. I've tried to use a pivot but doesn't give me the same result.