COUNTIF using multiple conditions?

Hello folks - would appreciate help with this, please!

I'm setting up a summary sheet to help track housing for a conference.
For each date I have a formula to track the number of rooms, i.e. for
January 7: =COUNTIF('Housing
List'!F:F,"1/07/2005")+B4-(COUNTIF('Housing List'!G:G,"1/07/2005"))

This counts the number of people arriving on the 7th, plus the number
people already arrived prior, minus anyone leaving on the 7th.

So now I need to break it down further, and use that same formula but
only count the single rooms, so the ones with "Single" in column H (and
then I need to do the same with Doubles).

How can I add that extra condition?

And THEN - oh this gets fun - I need to break it down one step further
and count the number of people from a particular group (code is in
column A) who have doubles for a particular night, and then for the
same group the number who have singles.
Please help!! :-)

Christina - Muse of Fire

0
12/29/2004 6:34:13 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
400 Views

Similar Articles

[PageSpeed] 19

Hi
use SUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

-- 
Regards
Frank Kabel
Frankfurt, Germany

Muse of Fire wrote:
> Hello folks - would appreciate help with this, please!
>
> I'm setting up a summary sheet to help track housing for a conference.
> For each date I have a formula to track the number of rooms, i.e. for
> January 7: =COUNTIF('Housing
> List'!F:F,"1/07/2005")+B4-(COUNTIF('Housing List'!G:G,"1/07/2005"))
>
> This counts the number of people arriving on the 7th, plus the number
> people already arrived prior, minus anyone leaving on the 7th.
>
> So now I need to break it down further, and use that same formula but
> only count the single rooms, so the ones with "Single" in column H
> (and then I need to do the same with Doubles).
>
> How can I add that extra condition?
>
> And THEN - oh this gets fun - I need to break it down one step further
> and count the number of people from a particular group (code is in
> column A) who have doubles for a particular night, and then for the
> same group the number who have singles.
> Please help!! :-)
>
> Christina - Muse of Fire 


0
frank.kabel (11126)
12/29/2004 6:47:38 PM
Thank you for the tip - but I'm still having trouble!

To count those arriving on 1/5/05 who have single rooms, I put:

=SUMPRODUCT(('Housing List'!F:F="1/05/2005")+('Housing
List'!H:H="Single"))

But it's returning a #NUM! error on both arrays???? I keep rereading
that website, but can't figure out what I'm doing wrong in my array???
Please help! Thank you :-)

Christina - Muse of Fire

0
12/29/2004 8:10:36 PM
Hi
try
=SUMPRODUCT(--('Housing List'!F1:F1000="1/05/2005"),--('Housing
List'!H1:H1000="Single"))

-- 
Regards
Frank Kabel
Frankfurt, Germany
"Muse of Fire" <museoffire@mindspring.com> schrieb im Newsbeitrag 
news:1104351036.215217.71370@c13g2000cwb.googlegroups.com...
> Thank you for the tip - but I'm still having trouble!
>
> To count those arriving on 1/5/05 who have single rooms, I put:
>
> =SUMPRODUCT(('Housing List'!F:F="1/05/2005")+('Housing
> List'!H:H="Single"))
>
> But it's returning a #NUM! error on both arrays???? I keep rereading
> that website, but can't figure out what I'm doing wrong in my array???
> Please help! Thank you :-)
>
> Christina - Muse of Fire
> 


0
frank.kabel (11126)
12/29/2004 8:49:52 PM
Reply:

Similar Artilces:

Want use results of Drop down list to look up a value
I am using Excel 2002 and I have a multisheet workbook and on the first sheet I have created a series of 6 drop down lists pulling from predefined named ranges. I also have limited subsequent choices based on what is selected in the other lists. The end result is the user will be selecting parameters that identify a product and now I am stuck with an easy way to look up the associated part number based on the parameters they selected. Essentially, once they have chosen the parameters in the list, I would like to create a macro that uses those values to locate the associated part...

How to used HOOK in OutLook Express
Hello All : I want to use hook control .When outlook express received new mail and remove the new eamil to myself mailbox . PS:Use C++ or HOOK .(not outlook express rule) Thanks Stiwin Lee ...

Using the "IF" function?
I setup a worksheet like below A B 1 Jan 123 2 Feb 543 3 Mar 654 4 Apr 456 5 May 645 6 Jun 543 7 Jul 0 8 Aug 0 9 Sep 0 10 Oct 0 11 Nov 0 12 Dec 0 13 When the end of the month, the user will enter some value in the B column, And now I would to setup a formula at A13, if the user enter the value in the B7, then the A13 will show Jul, when the user carry on to enter the value in the B8, then the A13 will change to show Aug, is it possible to setup a if formula or other formula rather than use the macro to doing this? Many Thanks. Ruff Try this: =INDEX...

Shared use of excel work books
Hi, Can anyone tell me "if I have an excel workbook (with multiple worksheets that report to the first worksheet) on a shared drive whether it can be set up for multiple users to access it and use it at the same time?" These users will be accessing their single worksheet to update it periodically with the first worksheet being the project leaders sheet where he gets to see all the other worksheets on his one sheet. -- Ian Hi Ian, You can do this by selcting "shareworkbook" from the tools menu and checking the 'allow changes by more than one use'. Beware th...

multiple outlets centralized Inventory needs POS solution
One of our customer has multiple outlets. Selling is conducted through the outlets. they have a centralized warehouse. A POS solution is required Contact your local RMS dealer he can help. Store ops in each location then HQ in warehouse. "Haresh_ahuja" <Haresh_ahuja@discussions.microsoft.com> wrote in message news:21B3A81A-2CDC-42A0-9327-9722661FAA41@microsoft.com... > One of our customer has multiple outlets. Selling is conducted through the > outlets. they have a centralized warehouse. A POS solution is required Haresh, You need a HQ for the warehouse and store op...

Bluetooth: After pair without using a code, CreateFile asks for co
I am trying to use a Bluetooth device in Vista Home Premium. The device does not have a pairing code or any way to enter one. I use "Add Wireless device" in the Bluetooth Devices control panel. My device is found and I choose "pair without using a code". The device pairs OK and its properties show under Services, Serial port ( SPP) 'AT Serial' COM4 But when I call CreateFile to open COM4, Vista shows a "click here" box informing me that a device is trying to connect. When I click the box, I get a dialog asking for the pairing code. How ca...

multiple or conditions
I have written syntax that doesn't seem to be picking up all my or conditions thus leaving me with an inaccurate answer. What am I doing wrong? =IF($I5>2.5,1,0)*OR($K5>2.5,1,0)*OR($M5>2.5,1,0)*OR($O5>2.5,1,0) *OR($Q5>2.5,1,0)*OR($J5>5,1,0)*OR($L5>5,1,0)*OR($N5>5,1,0) *OR($P5>5,1,0)*OR($R5>5,1,0) Look in HELP index for the proper syntax for OR -- Don Guillett SalesAid Software donaldb@281.com "lschuh" <lschuh@discussions.microsoft.com> wrote in message news:E3DCD1CE-4350-40E8-B1C5-0A1CEFCD5B27@microsoft.com... > I have written syntax ...

Example using MFC WinInet classes with POST of FILE and other INPUT data
I am looking for a good example for POSTing both FILE and other INPUT data to a URL. I understand that the FILE must use a multi-part encoding but I do not understand how the atcual file contents get POSTed or how the INPUT data gets POSTed also. I am guessing the INPUT data can be added as part of the query parameters to the URL. ...

conditional criteria in DSUM
I have a large spreadsheet of data containing part numbers. The part numbers are entered as numbers and then custom formatted to display dashes (i.e, part number 1234567890 displays as 123-456-7890). I am trying to use a DSUM formula that will add the beginning inventory (rows) for a month (column) for a particular group of parts. For example, all parts that begin with "3002" (i.e., 30020007409). I cannot for the life of me get the criteria to recognize the part number. I have tried criteria such as ">30020000000", and "3002*". Nothing is working. ...

in what order should i place multiple brochure pages?
i am wondering in what order should i lay out my pages in my brochure so it will print correctly . . . I would use one of the brochure templates, create, print it out and edit to my liking. -- Don Vancouver, USA "critic" <critic@discussions.microsoft.com> wrote in message news:BB2EA24A-EC9F-44EB-AB02-209FF66A1F77@microsoft.com... >i am wondering in what order should i lay out my pages in my brochure so it > will print correctly . . . ...

Conditional Autofill
Hi all! I've got this problem and it's really bugging me!! Say, I've got Sheet 1 (attached) and there's some formula (assume it' some VLOOKUP formula) on D3. I basically need some macro that woul autofill the formula in D3 down to all the other cells in column D EXCEPT for those cells with no corresponding value in the B column an those with "Total". Any help would be greatly appreciated. Thanks +------------------------------------------------------------------- |Filename: Sheet 1.JPG |Download: http://www.excel...

Can multiple cell results be displayed in a single cell?
Is it possible to display the contents of multiple separate cells in another single cell? If the results of several cells say B1, B2, and B3 are the amounts $10, $15 and $20, I would like to display them in cell A1 as follows: $10 $15 $20 I could do this statically by using text and the 'Alt|Enter' function within the cell. However, these amounts will change from time to time so I'd like a link. Is this possible or not? Thanks for any help. Brett Brett One way: In A1: ="$"&B1&CHAR(10)&"$"&B2&CHAR(10)&"$"&B3&am...

Using Custom Properties
I want to use custom properties to capture process task data while documenting business processes. I am having problems understanding how to make it work. The desired outcome is that I define 8-10 fields in the shape on the stencil so that anytime I drag that shape to the drawing it has all 8-10 fields to be filled in. Currently I can drag a shape to the page, define fields FOR THAT ONE INSTANCE OF THE SHAPE, but I cannot figure out how to change the custom properties on the master shape in a way that ANY TIME I drag the shape to the page the custom properties come with it. Please Help! ...

Importing vcalendar data to multiple users
I'm running Exchange 5.5. Several times a year I have to create a large group of users. I'd like to be able to pre-populate each user's calendar with pertinent calendar events. Is there a way to import this type of data in batch mode? See if http://www.slipstick.com/calendar/holidays.htm#company helps. ME wrote: > I'm running Exchange 5.5. Several times a year I have to create a > large group of users. I'd like to be able to pre-populate each user's > calendar with pertinent calendar events. > > Is there a way to import this type of data in batch...

Worksheet Change Event log for multiple cells
Dear All, I have a spreadsheet that is available here: http://www.filefactory.com/file/b02e5h4/n/Worksheet_change_event.zip (although I scanned it for viruses please make sure you do it again prior to opening it as I cannot guarantee it's worm free). I would like to create a log file in an additional sheet (hidden probably) that would record every activity from column E after clicking a button assigned to a cell in that column and show these records in a worksheet Totals in a specific row. For example: Column A from a worksheet „Totals” corresponds with column A in ...

Using Excel error #N/A in a formula
I would like to capture the error message #N/A in an IF statement Would like to use IF(MATCH(C1,Range,0)=#N/A,"OK","STOP") but it does not seem to work. What is the correct syntax to capture the #N/A error message and display "OK" instead of #N/A? =IF(ISNA(MATCH(C1,Range,0)),"OK","STOP") HTH Jason Atlanta, GA >-----Original Message----- >I would like to capture the error message #N/A in an IF statement. > >Would like to use IF(MATCH(C1,Range,0)=#N/A,"OK","STOP") but it does not seem to work. What is the...

How move multiplicity labels in UML diagrams?
How can you move the multiplicity labels on lines in UML diagrams? Also, is it possible to move the name labels? ...

Fonts name using CDC
Hello, I am drawing text using CDC (MFC control). How can I know all the Fonts available? does anyone know the font list available (if so please, and it is not so long, please write it, or at least tell me how to get it) thanks! Mathieu "Mathieu Fregeau" <mathieu.fregeau@polymtl.ca> wrote in message news:fAg0b.670$HB4.154408@news20.bellglobal.com... > Hello, > > I am drawing text using CDC (MFC control). How can I know all the Fonts > available? does anyone know the font list available (if so please, and it is > not so long, please write it, or at least te...

Using color to ignore cells in a formula?????
Is it possible to set up a formula in excel to ingore certain cells if you should change the color of that cell to a color. Example: =sum(a1:a50) if cell A25 was blue how can I ignore it so that it is not calculated? Mike A. Hi only with VBA. See: http://www.cpearson.com/excel/colors.htm and http://www.xldynamic.com/source/xld.ColourCounter.html -- Regards Frank Kabel Frankfurt, Germany "Mike A." <MikeA@discussions.microsoft.com> schrieb im Newsbeitrag news:65C99320-9FE3-4E11-81C2-C262B82B565B@microsoft.com... > Is it possible to set up a formula in excel to ingore cer...

Publisher should allow the deletion of multiple pages
I wish publisher would allow for the deletion of muliple pages much like the options available when printing pages. lejianzhong <lejianzhong@discussions.microsoft.com> was very recently heard to utter: > I wish publisher would allow for the deletion of muliple pages much > like the options available when printing pages. Suggestion: Try here: How to Contact the Microsoft Wish Program http://support.microsoft.com/default.aspx?scid=kb;en-us;Q114491 -- Ed Bennett - MVP Microsoft Publisher ...

passing dates in a conditional sum(if)
I have a workbook with 5 sheets. Sheet1 is Customers Sheet2 is YTD Sales 07 Sheet3 is Total Sales 06 Sheet4 is Total Sales 05 Sheet5 is Total Sales 04 Customer sheet has this basic formula in the columns for each year's sales based on the customer's number and the variable of the month that the sales was done. {=SUM(IF((Custnumber07=A3)*(Month07<=$J$1),amount07))} The problem is that the accounting program exports the invoices with the MMDDYY for each invoice. The $j$1 is the number of the month I limit it up to. I have a column in each sales sheet with the month() function to...

How to open multiple cases
Does anyone know if there's a way to open a case for several companies at one time? I've got a situation where I need to deploy a service bullitin for serveral companies. The title, description, etc.. are all the same. Thanks. Hmmm. Do you really need to open cases at first? If it's just a bulletin, then maybe you create a campaign to notify the appropriate customers, and then track the responses against the campaign. -- Microsoft Certified Professional - Dynamics CRM http://www.bturnkey.com "Coco" wrote: > Does anyone know if there's a way to open a ca...

Find and loop help-multiple columns
I an trying to write a macro to search for a cell value in one column and see if it occurs in other columns I'm having 2 problems: 1. How can I look from the last used row up to row 4 in "myrange+5" as set below? 2. How can I look in all other columns beside the "myrange+5" column named "Route Number(s)"? Am I completely off track?? With lastperiod 'Find the last used column myrange = ActiveSheet.UsedRange.Columns.Count ActiveSheet.Cells(1, myrange + 2).Select 'find the last row therow = ActiveSheet.Cells.Fi...

How to Create a Grid using CEditView
Hello There, I have an MDI Application. one of the view need to be Editable that should display the contents in a grid format. The Grid should be editable and should also be a scrollable. how to do it. Plz Reply Me Fast Hi Sreeram, I had sent you a msg at codeguru.com informing you that i've made a sample for you. I think that you havent read that msg. yet. I'll send it to you now. Regards, V.Girish ...

How do I print a font list using font name in the fonts?
I want to print a list of all of the fonts that I have using that particular font. See http://word.mvps.org/FAQs/Formatting/FontSampleGenerator.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Rich641" <Rich641@discussions.microsoft.com> wrote in message news:AB860C6E-32E3-4C5F-B695-0B479B9BE4CF@microsoft.com... >I want to print a list of all of the fonts that I have using that >particular > font. > ...