Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Median - Ignore blanks | Excel Discussion (Misc queries) | |||
Ignore blanks in multiplication formula | Excel Worksheet Functions | |||
DATA Validation (Ignore Blanks) | Excel Discussion (Misc queries) | |||
Data Validation - ignore blanks | Excel Discussion (Misc queries) | |||
USING IGNORE BLANKS IN FORMULA | Excel Discussion (Misc queries) |