Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Non-Blank Values in a Table but only for a range
I have a table of values like the following:
Column A=Date B=Name B=Problem C=Solved Date D=Solution I need to count the blank from column C but only from a certain date range in clumn A, for example count all the blanks in clumn C but only from the rows that have the date in clumn A between 1/1/2008 to 1/31/2008. Also with the same table count how many times a Name in column B apears but also only from the rows that have the date in clumn A between 1/1/2008 to 1/31/2008. Thank you for you help. -- Gabriel Camarena R. Delphi Tijuana IT Support |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Non-Blank Values in a Table but only for a range
Use this for your first problem:
=SUMPRODUCT((A2:A100=DATE(2008,1,1))*(A2:A100<=DA TE(2008,1,31)) *(C1:C100="")) I've assumed you have 100 rows, with a heading in row 1 - adjust ranges to suit your data. For your second problem: =SUMPRODUCT((A2:A100=DATE(2008,1,1))*(A2:A100<=DA TE(2008,1,31)) *(B1:B100=F1)) where F1 contains the name you are interested in. Hope this helps. Pete On Jul 11, 6:40*pm, GCRDelphi wrote: I have a table of values like the following: Column A=Date B=Name B=Problem C=Solved Date D=Solution I need to count the blank from column C but only from a certain date range in clumn A, for example count all the blanks in clumn C but only from the rows that have the date in clumn A between 1/1/2008 to 1/31/2008. Also with the same table count how many times a Name in column B apears but also only from the rows that have the date in clumn A between 1/1/2008 to 1/31/2008.. Thank you for you help. -- Gabriel Camarena R. Delphi Tijuana IT Support |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Non-Blank Values in a Table but only for a range
Thanks Pete!
Those worked great! Thanks again for your help. -- Gabriel Camarena R. Delphi Tijuana IT Support "Pete_UK" wrote: Use this for your first problem: =SUMPRODUCT((A2:A100=DATE(2008,1,1))*(A2:A100<=DA TE(2008,1,31)) *(C1:C100="")) I've assumed you have 100 rows, with a heading in row 1 - adjust ranges to suit your data. For your second problem: =SUMPRODUCT((A2:A100=DATE(2008,1,1))*(A2:A100<=DA TE(2008,1,31)) *(B1:B100=F1)) where F1 contains the name you are interested in. Hope this helps. Pete On Jul 11, 6:40 pm, GCRDelphi wrote: I have a table of values like the following: Column A=Date B=Name B=Problem C=Solved Date D=Solution I need to count the blank from column C but only from a certain date range in clumn A, for example count all the blanks in clumn C but only from the rows that have the date in clumn A between 1/1/2008 to 1/31/2008. Also with the same table count how many times a Name in column B apears but also only from the rows that have the date in clumn A between 1/1/2008 to 1/31/2008.. Thank you for you help. -- Gabriel Camarena R. Delphi Tijuana IT Support |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Non-Blank Values in a Table but only for a range
You're welcome - glad to be of help.
Pete On Jul 11, 8:23*pm, GCRDelphi wrote: Thanks Pete! Those worked great! Thanks again for your help. -- Gabriel Camarena R. Delphi Tijuana IT Support |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to count zeros and not blank in selected range? | Excel Worksheet Functions | |||
cell is blank then dont count range | Excel Discussion (Misc queries) | |||
Pivottable won't count blank values | Excel Discussion (Misc queries) | |||
Need assistance: Count non-blank within date range | Excel Worksheet Functions | |||
count of (blank) pivot table | Excel Discussion (Misc queries) |