Ignore Blanks in Countifs statement

  • Thread starter Thread starter Kcope8302
  • Start date Start date
K

Kcope8302

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

=COUNTIFS(Data!F$2:Data!F$199,2,Data!E$2:Data!E$199,E1,Data!g$2:Data!g$199,F1,Data!h$2:Data!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
 
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.
 
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?
 
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?
 
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
 
Back
Top