Ignore Blanks in Countifs statement

[Kcope8302]

I have a single countifs statement that has 3 lists referenced to it.

=COUNTIFS(Data!F$2ata!F$199,2,Data!E$2ata!E$199,E1,Data!g$2ata!g$199,F1,Data!h$2ata!h$199,G1)

I am trying to have this act as a query system. If one of these list
boxes(Cells E1,F1,G1) is left blank I would like it to be ignored. Therefore
I can have all 3 or a mix of the 3 boxes chosen to see what the difference is
in the data it pulls.

Can someone assist me with this?

Thanks

 

[T. Valko]

Try this array formula** :

=SUM(IF(E1<>"",Data!E2:E199=E1,ROW(Data!E2:E199)>0)*(Data!F2:F199=2)*IF(F1<>"",Data!G2:G199=F1,ROW(Data!G2:G199)>0)*IF(G1<>"",Data!H2:H199=G1,ROW(Data!H2:H199)>0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Kcope8302]

I have inputted the array formula. At this point information will only come
up when all the lists are blank. Once I do make a choice on one of the lists
it puts the count value to 0. So the only time information shows is when it
is doing a basic count feature(Data!F2:F199=2). It is not allowing me to
limit the information based on the lists.

Would you be able to assist me with this?

 

[T. Valko]

It sounds like E1:G1 are drop down lists?

The formula works in all of my tests.

Are you sure you entered the formula as an array using the key combination
of CTRL, SHIFT, ENTER?

 

[T. Valko]

Just so we're on the same page about this...

Here's how the formula works...

If cell E1 is empty then this test is ignored: Data!E2:E199=E1
If cell F1 is empty then this test is ignored: Data!G2:G199=F1
If cell G1 is empty then this test is ignored: Data!H2:H199=G1

If *all* 3 cells are empty then the only thing that gets tested is:
Data!F2:F199=2