This is my first post so first of all, wanted to say Hello to everyone.
Now I am working on a Google spreadsheet and have several tabs with names in column A so I made column A dynamic and as soon as I add a name of a tab in column A1 it should check 4 conditions in that tab
1. Check Column L and see if we have "Track-LPH12"
2. Check Column AG and see if we have Either "Waiting, Valid or Invalid"
3. Check Column Q and see if there is any thing (notes)
4. Check Column X and see if there is any thing (sub notes)
ARRAYFORMULA(IF($A1="",,COUNTIFS(INDIRECT($A1&"!$L:$L"),"Track-LPH12",INDIRECT($A1&"!$AG:$AG"),"Waiting",INDIRECT($A1&"!$Q:$Q"),"*",INDIRECT($A1&"!$X:$X"),"*")+COUNTIFS(INDIRECT($A1&"!$L:$L"),"Track-LPH12",INDIRECT($A1&"!$AG:$AG"),"Valid",INDIRECT($A1&"!$Q:$Q"),"*",INDIRECT($A1&"!$X:$X"),"*")+COUNTIFS(INDIRECT($A1&"!$L:$L"),"Track-LPH12",INDIRECT($A1&"!$AG:$AG"),"Invalid",INDIRECT($A1&"!$Q:$Q"),"*",INDIRECT($A1&"!$X:$X"),"*")))
Now the given formula works fine but if you see we have 3 conditions (Point 1, 3 and 4) which are same for all and only condition 2 changes "Waiting, Valid or Invalid", so I have to write full formula for 'Waiting' + same formula for 'Valid' and 'Invalid'
COUNTIFS(INDIRECT($A1&"!$L:$L"),"Track-LPH12",INDIRECT($A1&"!$AG:$AG"),"Waiting",INDIRECT($A1&"!$Q:$Q"),"*",INDIRECT($A1&"!$X:$X"),"*")+
COUNTIFS(INDIRECT($A1&"!$L:$L"),"Track-LPH12",INDIRECT($A1&"!$AG:$AG"),"Valid",INDIRECT($A1&"!$Q:$Q"),"*",INDIRECT($A1&"!$X:$X"),"*")+
COUNTIFS(INDIRECT($A1&"!$L:$L"),"Track-LPH12",INDIRECT($A1&"!$AG:$AG"),"Invalid",INDIRECT($A1&"!$Q:$Q"),"*",INDIRECT($A1&"!$X:$X"),"*")))
Is there a way that we can combine "Waiting, Valid or Invalid" instead of writing individual formula for each and then add them one by one, I am thinking to add 2~3 more data set for condition 2 so the formula will become too big and prone to errors.
I think there is a way to use sumproduct along with countifs but, can't make it work.
Please help
Thanks
Bookmarks