COUNTIFS question

[remythesausage]

Hi all, I am trying to write a formula where I can calculate all the 4a’s that are in the Gym, playing Basketball and are on a Tuesday. Hoping to find a formula where I would be able to drop down H3, H4, H5 to give me what I want at any time. Is it possible given that the dates are column headers?

Many thanks

 

[SanjayGMusafir]

You have 3D LOOKUP. So it would be better if you use SUMPRODUCT to count

=SUMPRODUCT(--(A:A=$H$2)*(B:B=$H$3)*($C1:$E1=$H$4))

 

[remythesausage]

You have 3D LOOKUP. So it would be better if you use SUMPRODUCT to count

=SUMPRODUCT(--(A:A=$H$2)*(B:B=$H$3)*($C1:$E1=$H$4))

Appreciate your response, the formula works well however when trying to total for only 4a the formula includes the 5a.
Is it possible to add another formula within this sumproduct to only calculate 4a? within the Gym/Basketball/Mon

 

[remythesausage]

Appreciate your response, the formula works well however when trying to total for only 4a the formula includes the 5a.
Is it possible to add another formula within this sumproduct to only calculate 4a? within the Gym/Basketball/Mon

 

[SanjayGMusafir]

within this sumproduct to only calculate 4a

Supposing you have entered 4a in cel H1

=SUMPRODUCT(--(C:C=$H$1)*(A:A=$H$2)*(B:B=$H$3)*($C1:$E1=$H$4))

 

[RoryA]

You could use:

=COUNTIFS(A:A,H2,B:B,H3,INDEX(C:E,0,MATCH(H4,$C$1:$E$1,0)),"4a")

 

[remythesausage]

Supposing you have entered 4a in cel H1

=SUMPRODUCT(--(C:C=$H$1)*(A:A=$H$2)*(B:B=$H$3)*($C1:$E1=$H$4))

That works! Thank you lots for your help

 

[remythesausage]

You could use:

=COUNTIFS(A:A,H2,B:B,H3,INDEX(C:E,0,MATCH(H4,$C$1:$E$1,0)),"4a")

That also helps! Thank you so much

 

[RoryA]

FWIW, it doesn't look like you have a lot of formulas like that, but using SUMPRODUCT on entire columns is not efficient.