Null value in multiple condition formula

Hi,

I wonder if anybody out there could tell me how to do this?

I am wanting to count the number of times in my worksheet 
a cell in column N is empty, WHEN the value of column C 
is "C" AND the value of column H (which contains 
dd/mm/yyyy data) is within the month of January.

I am using the formula:

=SUMPRODUCT((C2:C100="C")*(MONTH(H2:H100)=1))

to count those cells where Column C = "C" AND the month in 
column H = Jan (thanks to you wise and helpful people on 
the forum, I might add) but cannot work out how to do the 
null value in column N bit!

Any help would be great
thanks
Joe

0
7/24/2003 11:23:08 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
455 Views

Similar Articles

[PageSpeed] 37

=SUMPRODUCT((C2:C100="C")*(MONTH(H2:H100)=1)*(N2:N100=""))

>-----Original Message-----
>Hi,
>
>I wonder if anybody out there could tell me how to do 
this?
>
>I am wanting to count the number of times in my worksheet 
>a cell in column N is empty, WHEN the value of column C 
>is "C" AND the value of column H (which contains 
>dd/mm/yyyy data) is within the month of January.
>
>I am using the formula:
>
>=SUMPRODUCT((C2:C100="C")*(MONTH(H2:H100)=1))
>
>to count those cells where Column C = "C" AND the month 
in 
>column H = Jan (thanks to you wise and helpful people on 
>the forum, I might add) but cannot work out how to do the 
>null value in column N bit!
>
>Any help would be great
>thanks
>Joe
>
>.
>
0
mcurrie (76)
7/24/2003 11:37:27 AM
=SUMPRODUCT((C2:C100="C")*(MONTH(H2:H100)=1)*(H2:H100<>"")*(N2:N100))

in case some of H2:H100 is empty. It would even be better to have....

(DATEP(H2:H100)) instead of (H2:H100<>"")

Function DATEP(Cell) As Boolean
Application.Volatile
    DATEP = IsDate(Cell)
End Function

"Joe Miller" <joe-miller@supanet.com> wrote in message
news:131201c351d5$f5a96870$a101280a@phx.gbl...
> Hi,
>
> I wonder if anybody out there could tell me how to do this?
>
> I am wanting to count the number of times in my worksheet
> a cell in column N is empty, WHEN the value of column C
> is "C" AND the value of column H (which contains
> dd/mm/yyyy data) is within the month of January.
>
> I am using the formula:
>
> =SUMPRODUCT((C2:C100="C")*(MONTH(H2:H100)=1))
>
> to count those cells where Column C = "C" AND the month in
> column H = Jan (thanks to you wise and helpful people on
> the forum, I might add) but cannot work out how to do the
> null value in column N bit!
>
> Any help would be great
> thanks
> Joe
>


0
akyurek (248)
7/24/2003 1:02:09 PM
Reply:

Similar Artilces:

spin button value
Hi Does the value in spinbutton properties have to be numeric or can it be a day eg mon tues wed etc if so how do I dio this Thanks tina A Forms toolbar spinner control returns an integer. You can have another cell with a formula that references the cell linked to the spinner like =CHOOSE(A1,"Sun","Mon","Tues","Weds","Thurs","Fri","Sat") Here A1 is linked to the spinner. You'd want to limit the spinner's minimum value to 1 and maximum value to 7 in this case. -- Jim Rech Excel MVP "tina" <tina@...

Totalling Formulas
I am creating a spreadsheet that calculates a person's time on the job. It looks like this Inclusive Dates (Begin) Inclusive Dates (End) Totals 1/27/1996 3/20/1996 0 years, 1 months, 22 days Inclusive Dates (Begin) Inclusive Dates (End) 3/21/1996 2/6/1998 1 years, 10 months, 16 days It already calculates the time between the Inclusive Dates (Begin) and Inclusive Dates (End), now i want it to total to totals into a block which reflects the same way like: Inclusive Dates (Begin) Inclusive Dates (End) Totals 1/27/1996 3/20/1996 ...

Null Is Null
I've got a text box in a report that's not returning properly. Here's its Control Source: =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]! [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]! [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"") This returns #Error. It's definitely my IIf statement as it returns properly when removed however there are times when [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this subreport will not appear if no records exist. I suspect that this is the problem. How can I write this Control Source t...

if A1=Null and B1=A1 why is result in B1=0 ??
if A1=Null and B1=A1 why is result in B1=0 ?? because of this I get wrong result for average calculations: average for (6,8,0) <> average for (6,8,null) Use =IF(A1="","",A1) -- HTH RP (remove nothere from the email address if mailing direct) "WGeorg" <WGeorg@discussions.microsoft.com> wrote in message news:09FCCF25-D0DC-4B1E-BFA1-CEF9A9327C46@microsoft.com... > if A1=Null and B1=A1 why is result in B1=0 ?? > because of this I get wrong result for average calculations: average for > (6,8,0) <> average for (6,8,null) Use this ...

Syntax for Is Not Null ?
When one of our forms is opened (Current event) I want a message box fired by certain conditions (that the Ref control does have a value, but the Country control is empty). I have tried various bits of code along the lines shown below, but keep getting runtime errors. Wot's wrong with the following, please? If [Me.Ref] is not null and [Me.Country] = "" Then MsgBox "Please enter the country!" Many thanks CW CW - Try this: If (not isnull([Me.Ref])) AND IsNull([Me.Country]) Then MsgBox "Please enter the country!" -- Daryl S ...

When is a Null not a Null?
I have a form which is used for adding and entering data depending on where it is called from. When called in add mode (acFormAdd) a blank form is openned. When the cursor enters the text box [Name] on the form field I am trying test whether it is empty so that a search form can be openned if a new name is to be added. (I don't want the search form to open in cases when the form already has data in and I am editing rather than adding data). The form is based on a query and the data displayed in [Name] is the result of a calculated field in the query which concatenates firstname and surn...

Enabling a control based on multiple criteria
This code will enable txtReason if cboFavColor = "Green," and disable txtReason if cboFavColor <> "Green": Me.txtColors.Enabled = IIf(Me.cboFavColor = "Green", -1, 0) If I want to expand the criteria to include "Green" OR "Blue", can I do something like: Me.txtColors.Enabled = IIf(Me.cboFavColor = "Green" OR "Blue", -1, 0) This doesn't work but I suspect it's because I have the syntax wrong. Suggestions? Me.txtColors.Enabled = IIf(Me.cboFavColor = "Green" OR Me.cboFavCo...

retrieving the XmlEnumAttribute and the enum-values (2)
This post is a followup to the thread "retrieving the XmlEnumAttribute values for an Enum" in this same forum earlier this month, since my last query in that thread went unanswered. I have an enum defined (DotNet v2) as public enum velocityUom { [System.Xml.Serialization.XmlEnumAttribute("m/s")] ms = 21, [System.Xml.Serialization.XmlEnumAttribute("cm/a")] cma, ... } My original post was to ask how I could retrieve the XmlEnumAttribute values for this Enum so that I could create a combo box with values like "m/s", &qu...

What is the Null character?
What is the character sequence that Excel uses to represent an empty cell? I am using a formula and when it returns false, I would like for it to represent the cell as if it was empty. I have tried using "" as an empty cell, but excel recognizes that this is a value, not an empty cell. You can't do what you want to do. A formula always returns *something*. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Quinn Ramsey" <Quinn Ramsey@discussions.microsoft.com> wrote in message news:D67F6311-0CCA-48B5-A...

Formatting a cell for color that has a formula also
I am using this formula =If ($A$1="","",A1+1) so that when i put a date in it adds the next date to it. I want to be able to get Saturday and Sunday to change to a yellow color to note that its a weekend date can this be done? I can do it manually but would like to be able to do it automatically if possible. Thanks Karl Select your range (all at once) With A1 the activecell, do: format|Conditional formatting formula is: =weekday(a1,2)>5 give it a nice format. I like to give date cells a custom format of: dddd* mm/dd/yyyy Karl wrote: > > I am using this...

Passing Values between forms
I am trying to link to forms. I have a button on the main form (frmCompanies) that needs to open another form when a button is clicked. The new form that opens is frmScheduledActivities. When it open it needs to check to see if there is an existing record (scheduled activity) for the company and if there is no scheduled activity be set to add a new record. When the button is clicked on the main company form (on the on click event) this code runs: Private Sub cmdCallBack_Click() On Error GoTo Err_cmdCallBack_Click Dim stDocName As String Dim stLinkCriteria As String...

Date formula #12
Hello All: I have a spreadsheet that I need to have the dates for the month. On this application I needed the First of the month in one cell and the last day of the month listed in another. I inserted a calendar that allows me to put the selected date into the active cell. (Formatted ddd mmmm dd yyyy) The I use the formula =DATE(YEAR(C6),MONTH(C6)+1,DAY(C6)-1) to enter the last day of the month into the appropriate cell. (Formatted mmmm dd yy) The problem I am having is that this formula works on most of the dates in a month but gives me a #NUM! Error when it is asked to give me the l...

Charting null values
I have a chart that is plotting null values. I tried the formula =if(C25="",N/A(),Sum(C22:C25)). I've also deleted the formula to create a completely null cell. I've been to Tools, Options, Chart and selected Plot empty cells as Not plotted, zero and Interpollated As well as turning Plot Visible Cells only (turned it on and off) Nothing seems to make a difference. Any suggestions? Stephanie, what kind of chart are you using? Can you give an example of how your data table looks like? br, Henk "StephanieH" wrote: > I have a chart that is plotting ...

How to Link a cell in an Excel spread sheet to a text value in Visio 2007
I would like to have a text value in Visio take the value of a cell in an Excell spread sheet. Is there a way to do this, at least on opening the Visio Document? Thanks in advance, Marco UCO Lick Observatory Laboratory for Adaptive Optics visio 2007 professional http://office.microsoft.com/en-us/visio/HA100518191033.aspx al "Marco" <null@null.net> wrote in message news:uCcafQkuKHA.1796@TK2MSFTNGP02.phx.gbl... > I would like to have a text value in Visio take the value of a cell in an > Excell spread sheet. Is there a way to do this, at leas...

NULL values
Working with a downloaded table form a Lotus Notes contact manager. Now in access call tblclients. I query certain info. to update the records. BUT...'Is Null' does not work. It comes back empty and there are definitely empty contents in the fields I chose. I know how to do this as it works in my other tables on other databases. The only difference I can see in this table as opposed to tables created directly in Access is the Field Design property called Unicode Compression saying No instead of the default Yes. I changed the Unicode to Yes in case this had something to do with it and ...

How can I check a combo box content or value or something
Hi. I need to check if my combobox has something on it, what I mean is if my users choose any value. I need something looked like: if combo1 then msgbox "you must choose an option on Combo1" exit sub end if How can I do something looked like? Regards, Marco Marco, In the AfterUpdate event of the combo type, if IsNull(combo1.text) then msgbox "you must choose an option on Combo1" End If "Marco" <Marco@discussions.microsoft.com> wrote in message news:92B57DBB-74BC-4177-AB68-A87153654F34@microsoft.com... > Hi. I need to check if my combobox h...

How do I identify if a cell in excel contains a formula or a numbe
I would like to identify some how if a cell contains just a written number (ex "25000") or contains a formula. I would like to do this because I have an array where some cell contains formulas but some are numbers and I would like to be able to use conditional formating to highlight the cells that just have a number. Regards /Tobias Click on the cell and look at the formula bar. The formula or the value will appear there. Ian "Tobias Andersson" <Tobias Andersson@discussions.microsoft.com> wrote in message news:739C49F9-91D7-4F0C-8581-FF04AF3E9375@microsoft.com...

Average formula #6
What formula do I use to calculate a weekly average as the monthly tota changes? Example: july total value divided by 28weeks, august value divided b 32 weeks, sept value divided by 36 weeks and so on In other words, a weekly average as each month ends and the value i entered I hope someone understands this and can help thanks so much lesli -- onyx481 ----------------------------------------------------------------------- onyx4813's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2630 View this thread: http://www.excelforum.com/showthread.php?threadid=47137 As...

Taking the Mode based on cell value
Could you provide a few more details about what you want to do? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "sip8316" <sip8316@discussions.microsoft.com> wrote in message news:E3F4DE24-C5E8-4911-96E6-E61A1E56DEF0@microsoft.com... > ...

How to MATCH value up a column
I need to match/find the first matching cell up a column. Ideas? TIA! Hi I don't know what you are looking for, but see: MATCH(A2,A1:F1,0) or VLOOKUP(A3,Sheet2!A:B,2,FALSE) -- Please click "yes" if this post helped you! Greatly appreciated Eva "DevourU" wrote: > I need to match/find the first matching cell up a column. Ideas? TIA! Thankx for the reply. Here is my formula:=IF(A11>A10,C10,(IF(A11=A10,A10,"?"))). I need to have IF(A11<A10, search up column A for 1st match, example A4, display next column B4. Clear as mu...

VLOOKUP: Retrieving Values
Vlookup: I am trying to lookup a value ( SCOTLAND ) on a spreadsheet called ( Data ) in order to populate ceratin cells on my other spreadsheet ( Spreadone ). If the value (SCOTLAND) is located in (Data) I want it to copy the values of specific cells (not all of them ) from the same row into ( Spreadone ). So : CHECK COLUMN N (CLASS) IN SPREADSHEET (DATA) FOR STRING (SCOTLAND) IF THE STRING = SCOTLAND THEN IN SPREADSHEET (Spreadone) POPULATE CELL A7:A25 = ACCOUNT NO (from Data) CELL B7:B25 = CLIENT (from Data) CELL C7:C25 = CUSIP (from Data) CELL D7:D25 = QTY (from Data) CELL E7:E25...

rst.findfirst not working with two conditions
i have a table with two date fields and i opened a dao recordset (based on a query) on it .Now i want to search whether a particular date group falls between these dates and the rate for that period.Dates in UK style. FromDate ToDate Rate 01/01/2009 31/03/2009 7570 01/05/2009 30/11/2009 7770 01/12/2009 31/12/2009 7970 //variables declared here //SQL is defined here to limit it the recordset Set rst = currentdb.OpenRecordset(strSQL, dbOpenDynaset) MyFromDAte = 01/06/2009 MyToDate = 31/10/2009 rst.Findfirst "[FromDate] &...

Can I set an audio alert that triggers as Excel cell value chgs?
I would like to add an audio alert to a spreadsheet cell whenever it changes to a specific value? Can this be done? ....I'm not familiar with any audio alerts but, you can use conditional formatting to the cell's format change when it value changes. Go to Format...Conditional Formatting. You can then either set a value or use a formula and set the format (what it will look like when the cell is the value that you set). "SellUnHi" wrote: > I would like to add an audio alert to a spreadsheet cell whenever it changes > to a specific value? Can this be done? L...

macro to action formula
sir, this is my first qst. I have a workbook with sheets for each date x so almost 30sheets x and I am using cpy data macro to copy all these date to one sheet x whenever I run the macro this sheet is newly created with the latest date x I want to use sum and sumif formulas to calculate this sheet data x can u adv a macro to do this -- pvkkutty new to discussion group however a freequent reader of discussion group posts It would be better to just have ONE sheet with all and use data>filter to see for each day desired. If desired, send your file to my address ...

PrimaryContactID NULL
Hello, i have discovered a strange behavior of CRM regarding the relationship between accounts and contacts: Some of my accounts show up a referenced contact but do not have a PrimaryContactID GUID in the databasefield. Both where converted from a lead which is refrenced with the correct GUID in the OriginatingLeadID Field in the database? Any idea how this can happen? Regards Sebastian When you convert a lead into an account/contact the contact references the account throught the parent customer field. The account does not reference the contact (this would be the primary contact fi...