Ignore Blanks in Countifs statement
 

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$19 9,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

Ignore Blanks in Countifs statement
 

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(D ata!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.

--
Biff
Microsoft Excel MVP


"Kcope8302" wrote in message
...
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$19 9,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

Ignore Blanks in Countifs statement
 

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" wrote:

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(D ata!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.

--
Biff
Microsoft Excel MVP


"Kcope8302" wrote in message
...
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$19 9,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

Ignore Blanks in Countifs statement
 

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?

--
Biff
Microsoft Excel MVP


"Kcope8302" wrote in message
...
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" wrote:

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(D ata!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.

--
Biff
Microsoft Excel MVP


"Kcope8302" wrote in message
...
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$19 9,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

Ignore Blanks in Countifs statement
 

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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?

--
Biff
Microsoft Excel MVP


"Kcope8302" wrote in message
...
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" wrote:

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(D ata!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.

--
Biff
Microsoft Excel MVP


"Kcope8302" wrote in message
...
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$19 9,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