2 questions regarding count formula

If I have the following data

           A                            B                    C
1    Personnel	  group type       p't number
2    Melinda	        1	                4
3    Sean                           1	                5
4    Melinda	        1	                6
5    Sean                           2	               30
6    Fay	                        3	                8
7    Melinda                       1                     32

1.  How shall I contruct my formula to count the number of data that satisfy 
the following criteria:
1.  personnel is Melinda
2.  group type is 1
3.  p't number is between 4 and 8

2.  When designing the formula, can the formula be extended in such a way 
that even if I add more data to the table above, I can still get an accurate 
count without redefining my data range in the formula?

Thanks.
Melinda
-2
Melinda (30)
11/3/2006 2:16:02 AM
excel.newusers 15348 articles. 1 followers. Follow

8 Replies
505 Views

Similar Articles

[PageSpeed] 35

Hi Melinda,

You can use the sumproduct function with a range that is sufficient to 
include new data e.g.

=SUMPRODUCT(--(A2:A200="Melinda"),--(B2:B200=1),--(C2:C200>=4),--(C2:C200<=8))

Hth
Anthony


"Melinda" wrote:

> If I have the following data
> 
>            A                            B                    C
> 1    Personnel	  group type       p't number
> 2    Melinda	        1	                4
> 3    Sean                           1	                5
> 4    Melinda	        1	                6
> 5    Sean                           2	               30
> 6    Fay	                        3	                8
> 7    Melinda                       1                     32
> 
> 1.  How shall I contruct my formula to count the number of data that satisfy 
> the following criteria:
> 1.  personnel is Melinda
> 2.  group type is 1
> 3.  p't number is between 4 and 8
> 
> 2.  When designing the formula, can the formula be extended in such a way 
> that even if I add more data to the table above, I can still get an accurate 
> count without redefining my data range in the formula?
> 
> Thanks.
> Melinda
-1
AnthonyD (17)
11/3/2006 2:34:01 AM
Thanks Anthony for your prompt reply.  However, this formula doesn't work.  
Using the table below to test, the formula returns 0 instead of 2.

Any suggestions?

Melinda

"Anthony D" wrote:

> Hi Melinda,
> 
> You can use the sumproduct function with a range that is sufficient to 
> include new data e.g.
> 
> =SUMPRODUCT(--(A2:A200="Melinda"),--(B2:B200=1),--(C2:C200>=4),--(C2:C200<=8))
> 
> Hth
> Anthony
> 
> 
> "Melinda" wrote:
> 
> > If I have the following data
> > 
> >            A                            B                    C
> > 1    Personnel	  group type       p't number
> > 2    Melinda	        1	                4
> > 3    Sean                           1	                5
> > 4    Melinda	        1	                6
> > 5    Sean                           2	               30
> > 6    Fay	                        3	                8
> > 7    Melinda                       1                     32
> > 
> > 1.  How shall I contruct my formula to count the number of data that satisfy 
> > the following criteria:
> > 1.  personnel is Melinda
> > 2.  group type is 1
> > 3.  p't number is between 4 and 8
> > 
> > 2.  When designing the formula, can the formula be extended in such a way 
> > that even if I add more data to the table above, I can still get an accurate 
> > count without redefining my data range in the formula?
> > 
> > Thanks.
> > Melinda
3
Melinda (30)
11/3/2006 2:49:02 AM
> Using the table below to test, the formula returns 0 instead of 2.

Anthony's suggestion should work. The problem lies in your data. There may 
be text numbers in the numerics in cols B & C, and/or there may be extra 
white spaces in the names in col A. Any of these could throw the matching 
off. 

This tweak should cover all possibilities:
=SUMPRODUCT(--(TRIM(A2:A200)="Melinda"),--(B2:B200+0=1),--(C2:C200+0>=4),--(C2:C200+0<=8))
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Melinda" wrote:
> Thanks Anthony for your prompt reply.  However, this formula doesn't work.  
> Using the table below to test, the formula returns 0 instead of 2.
0
demechanik (4693)
11/3/2006 3:13:01 AM
Thanks.  It solve the first part of my question.  However, do I have to 
define the row number in my formula in order to let it work?  Can I just 
define the column which I want instead?  Will this results in changing the 
formula?

Melinda

"Max" wrote:

> > Using the table below to test, the formula returns 0 instead of 2.
> 
> Anthony's suggestion should work. The problem lies in your data. There may 
> be text numbers in the numerics in cols B & C, and/or there may be extra 
> white spaces in the names in col A. Any of these could throw the matching 
> off. 
> 
> This tweak should cover all possibilities:
> =SUMPRODUCT(--(TRIM(A2:A200)="Melinda"),--(B2:B200+0=1),--(C2:C200+0>=4),--(C2:C200+0<=8))
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Melinda" wrote:
> > Thanks Anthony for your prompt reply.  However, this formula doesn't work.  
> > Using the table below to test, the formula returns 0 instead of 2.
0
Melinda (30)
11/3/2006 3:36:03 AM
The ranges need to be defined and should be consistent in structure, as per 
the example suggested.

SUMPRODUCT doesn't accept entire col references, eg: A:A, B:B.

In defining the ranges, try to use the smallest possible range sizes, 
otherwise recalc performance would be slow.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"Melinda" <Melinda@discussions.microsoft.com> wrote in message 
news:E7E11895-8036-4CC7-B2EF-7F1842D0E7FA@microsoft.com...
> Thanks.  It solve the first part of my question.  However, do I have to
> define the row number in my formula in order to let it work?  Can I just
> define the column which I want instead?  Will this results in changing the
> formula?
>
> Melinda 


0
demechanik (4693)
11/3/2006 3:45:08 AM
what does the "--" mean?  I found that if I remove that from my formula, it 
will not work.  Does it have special meaning?

Melinda

"Anthony D" wrote:

> Hi Melinda,
> 
> You can use the sumproduct function with a range that is sufficient to 
> include new data e.g.
> 
> =SUMPRODUCT(--(A2:A200="Melinda"),--(B2:B200=1),--(C2:C200>=4),--(C2:C200<=8))
> 
> Hth
> Anthony
> 
> 
> "Melinda" wrote:
> 
> > If I have the following data
> > 
> >            A                            B                    C
> > 1    Personnel	  group type       p't number
> > 2    Melinda	        1	                4
> > 3    Sean                           1	                5
> > 4    Melinda	        1	                6
> > 5    Sean                           2	               30
> > 6    Fay	                        3	                8
> > 7    Melinda                       1                     32
> > 
> > 1.  How shall I contruct my formula to count the number of data that satisfy 
> > the following criteria:
> > 1.  personnel is Melinda
> > 2.  group type is 1
> > 3.  p't number is between 4 and 8
> > 
> > 2.  When designing the formula, can the formula be extended in such a way 
> > that even if I add more data to the table above, I can still get an accurate 
> > count without redefining my data range in the formula?
> > 
> > Thanks.
> > Melinda
0
Melinda (30)
11/3/2006 3:59:02 AM
The double unary "--" gently coerces the TRUEs/FALSEs returned by the 
various match expressions (eg: TRIM(A2:A200)="Melinda") into numeric 
1's/0's.

A variation of the earlier expression (without using "--")
which would work equally well here is:

=SUMPRODUCT((TRIM(A2:A200)="Melinda")*(B2:B200+0=1)*(C2:C200+0>=4)*(C2:C200+0<=8))

-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"Melinda" <Melinda@discussions.microsoft.com> wrote in message 
news:0B494C10-CC1E-409E-B221-031172F75DAC@microsoft.com...
> what does the "--" mean?  I found that if I remove that from my formula, 
> it
> will not work.  Does it have special meaning?
>
> Melinda 


0
demechanik (4693)
11/3/2006 4:10:51 AM
additional details here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


"Melinda" wrote:

> what does the "--" mean?  I found that if I remove that from my formula, it 
> will not work.  Does it have special meaning?
> 
> Melinda
> 
> "Anthony D" wrote:
> 
> > Hi Melinda,
> > 
> > You can use the sumproduct function with a range that is sufficient to 
> > include new data e.g.
> > 
> > =SUMPRODUCT(--(A2:A200="Melinda"),--(B2:B200=1),--(C2:C200>=4),--(C2:C200<=8))
> > 
> > Hth
> > Anthony
> > 
> > 
> > "Melinda" wrote:
> > 
> > > If I have the following data
> > > 
> > >            A                            B                    C
> > > 1    Personnel	  group type       p't number
> > > 2    Melinda	        1	                4
> > > 3    Sean                           1	                5
> > > 4    Melinda	        1	                6
> > > 5    Sean                           2	               30
> > > 6    Fay	                        3	                8
> > > 7    Melinda                       1                     32
> > > 
> > > 1.  How shall I contruct my formula to count the number of data that satisfy 
> > > the following criteria:
> > > 1.  personnel is Melinda
> > > 2.  group type is 1
> > > 3.  p't number is between 4 and 8
> > > 
> > > 2.  When designing the formula, can the formula be extended in such a way 
> > > that even if I add more data to the table above, I can still get an accurate 
> > > count without redefining my data range in the formula?
> > > 
> > > Thanks.
> > > Melinda
0
jmb (271)
11/3/2006 4:44:01 AM
Reply:

Similar Artilces:

Major help needed!!!! #2
Thanks for the post GE, I was able to find another computer with exce 97 still loaded. I opened the existing spreadheet and went through th tools options and unchecked th 1904 date option. Went back to m updated computer and the dates came across just fine. Also thanks Don, your comment will be noted. I was in a rushed an panic'd state : -- KyDean ----------------------------------------------------------------------- KyDeano's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1556 View this thread: http://www.excelforum.com/showthread.php?threadid=27128 ...

error 0x800ccc13 #2
I have Outlook 2000 installed on WinXP home. After installing modem software (for fax purposes) I now receive error 0x800ccc13 when performing 'send receive' in Outlook. Outlook Express now also fails with 0x800ccc0f error. I've disabled all spywatch and filtering software and tested Winsock using Netdiag and Winsock passes but there is a fail for NetBT transports test failed (I'm assuming because I removed all services other than TCP/IP.) I've reinstalled Outlook Express using the procedure from #318378 http://support.microsoft.com/default.aspx?scid=kb;en-us;318378&...

Outlook 2003, selecting Contacts question, long post, Please give feedback
We are using Outlook to manage a rather large client base. (it's not the best program for our use, but I'm not the boss) Right now the number stands at 900, with a possible 500+ more by the time we're done entering. I have to select clients each day and print them, 2 per page. I have to be in Detailed View to get the info I need to print. The "clicking and selecting" method is clumsy because if I click on the Initial column at the far right, it de-selects those already selected. Most days, I need to print 20-30 pages, (40-60 clients) I've resorted to just selecti...

MS Publisher #2
Does anyone know how to open an MS Publisher 2000 document with MS Publisher 97? You cannot open a Publisher 2000 document in Publisher 97. You need the same or later version of Publisher to open Publisher documents. -- Why is it that people don't realize that they will probably have a problem opening a newer file with an older program? Rarely does that ever happen. "Riobard" <morgan.riobard@sivh.ie> wrote in message news:12b801c38ceb$868aea80$a401280a@phx.gbl... > Does anyone know how to open an MS Publisher 2000 document > with MS Publisher 97? Because t...

Help ! #2
Hi, I have developed an application in VC++ where spelling of a word will be checked in Excel (English is default). I have used Automation for this. I want to use the same application for German and French languages. Could someone help me on how to select the language programmatically so that selected language's dictionary will be activated and spelling of a word will be checked in that selected language's dictionary? TIA, Gary ...

Error adding users #2
Hello all: I am working with a new installation of MSCRM, and when I try to add a user I get the "AN error ahs occurred, please contact your system administrator" message. So I enabled tracing, and I have pasted the error details from the trace file below... Does anyone have any ideas? John. >MSCRM Application Error Report: -------------------------------------------------------------------------------------------------------- Error: Index was out of range. Must be non-negative and less than the size of the collection. >Parameter name: index Error Message: Index was ou...

RMS 2.0 Manual
I have a general question. I remember over year ago when we were buying RMS license we were receivig license+box with manual, user guide and Instalation CD. From some time whenever we order new license there is A2 cartboard "envelope" comming which have another A3 soft envelope in it and eventually this one ontains 10cm x 10cm RMS license sheet. Can someoe tell me is it just us or everybody have it this way. Before after we did training for customer he had Manual/User Guide to look back into if he forgot about something, I know that this manual is exact printout of ...

Qty sold/Qty available #2
Any way of doing this on a top performer report? I would like to see the qty sold and qty available per item, not individual sales. THANKS! CT >-----Original Message----- >CT, > >Detailed Sales Report and add the On Hand column. (right click on the >report, Show/Hide Columns) > >-- >Jeff > >Get Secure! - www.microsoft.com/security > >===================================================== > >When responding to posts, please "Reply to Group" via >your newsreader so that others may learn and benefit >from your issue. > >=======...

Button for goal seek?! #2
Hello I have the following data: 5 6 12 15 20 Total of all those added I will have to set a goal seek button so that i can put the total i want and then change the cell i want i tried using a macro but i couldnt do it... I just want to do it using a click of a button instead of going to menu>goal seek>etc etc Thanks a lot From John -- Neo1 ------------------------------------------------------------------------ Neo1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30329 View this thread: http://www.excelforum.com/showthread.php?threadid=515571 Any ...

FTP #2
Greetings. I have an MFC application working just fine using ftp with CInternetSession and CFtpConnection, and I'm using windows ftp server. this app needs to check file's size and dates where CFtpFileFind seems to works perfectly. I have some doubts: 1. this app seems to work fine with filezila ftp server... is there somethig I have to know working with other ftp servers specially servers runing on linux? I have seen that list files is not standard... 2. MFC can handle FTPS?? if not, does any one knows a good library? I'm starting to see libcurl. Any suggestions are welc...

2 cells 2 colors
Hi All, Here's my glitch: I've got 2 dropdown/combo boxes in a user form. Both offer variou color options. The 1st one colors an active cell the correct color tha is chosen. The 2nd dropdown colors the cell directly to the right or th active cell in the same manner. My code is pretty crude & not ver efficient, but it works correctly. The problem is when color 2 is no chosen. Currently, if a color isn't chosen, I can put an "X" in th cell, or color it a certain color, but what I want is to color i whatever the color of the active cell is. Any ideas? Thanks -- S...

Forms.dic #2
Hi, I might have missed out something. After installing MGP v8.0 SP5 on the workstation, I then I clicked on Modifier, the system created a file called forms.dic. After this, I can't open certain forms like Sales Transaction Windows, Sales Order Fulfillment, Inventory Transaction, etc. Other forms like Item Class ID, Journal Entry are fine. Anything I miss out here? TIA, Sugih Were these forms modified? Is security pointing to the modified forms? The new forms.dic will be empty and not contain these forms. Most firms either copy a complete forms.dic from a working workstation ...

Outlook 2007
WinXP Pro SP3 Outlook 2007 - Pop3 Can outlook 2007 have mail stores (PST) greater than 2 GB? I have a friend who keeps all for his business and is pushing the limit. I was told that Outlook 2007 raised the limit. Any clarification is appreciated. -- ***************** John Lenz JohnLenz@comcast.net 20Gb + if using the new, default, pst file type of Unicode which is the default in Outlook 2003 and later If however you upgraded outlook from a pre 2003 version, then it maybe that you are still using the old outlook format data file which has the size limitation. Within Outlook, select ...

excel formula problem #4
Thanks For That, It Works Brilliantly, Youre A Geniu -- jimbo69 ----------------------------------------------------------------------- jimbo693's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1421 View this thread: http://www.excelforum.com/showthread.php?threadid=25844 ...

Office 97 to 2003: Excel: enter data w/fixed 2 decimal. 2000. be. #2
When I enter number "2000." I get 20.00 - yes, set decimals at 2 but earlier excel would accept change if you entered decimal with number. this is slowing down a budget prep and I'd sure like some help - online and tutorials say "enter decimal" but this is not working. what am I doing wrong? ...

Questions related to code customization
-Where can I add my Java Script code ? -how can I use CRM SDK?(developpe classes or ASP.NET using the SDK libraries?) Hi Ahmed, You can add the script from settings->customizations->Customize Entity->Select the Entity->Goto Forms n Views-Select the Form-Click Form Properties to add Script or select the field then Change Properties to add Script. You can add it under Events. You can create .Net Application and access the MS CRM using web services.. you can find sample codes along with SDK files. The questions were pretty much covering the customization of MS CRM and requires hel...

matrix item in rms 2.0
When creating a matrix item in 2.0, and selecting the option to show the dimensions of the size and color with the description, I get a error message that "itemlookup code or item description exeeds the allowed length. do you wish to continue truncating the lookup code or description." However with the old version I was able to do this. Is the description field more restricted in size? Also it whould be easier to be able to enter the matrix reorder #,minimum order & case quantity when you create a matrx item, for now i have to do it with the wizard. Does anybody know ...

One Control for 2 codes
My Combo Box cbGSTOptions selects either tax or no tax but after making my selection I need to update with another control form the calculation to work Control 2 is under the line==== Can I incorporate these two code to just the Afterupdate of my Combo Box ---------------------------------------------------------------------- Option Compare Database Option Explicit Dim recInvoice_ItMdt As ADODB.Recordset Dim recInvoice As ADODB.Recordset, dblGSTContentsValue As Double, lngIntermediateID As Long Dim dblGSTOptionsValue As Double Dim bModify As Boolean Dim ynCheque As Boolean, ln...

Exchange 2007 Beta 2 question
Hi I have the mailbox, CA, hub and UM on one server. I am trying to create a new email life cycle policy, but the option to do this cannot be found. I right click on the mailbox icon in EMC under "organization" but the option is not listed. The only listed options are. Also the tab in ESM for this is not there New address list New managed custom folder new managed fodler mailbox policy new offline address list On Wed, 23 Aug 2006 13:58:02 -0700, skip <skip@discussions.microsoft.com> wrote: >Hi > >I have the mailbox, CA, hub and UM on one server. I am trying to...

2 X 2 Splitter Windows #2
What is wrong with the following code? I am trying to create a 2 X 2 splitter window. It should look like this. CMyTreeView | CMyListView __________________|___________________ | CPictureView | CDetailView It compiles correctly but when I run it I get an error in the WinSplit routine of MFC. if(!m_wndSplitterWnd1.CreateStatic(this,1,2)) return FALSE; if(!m_wndSplitterWnd1.CreateView(0,0, RUNTIME_CLASS(CMyTreeView), CSize(0,0), pContext)) return FALSE; if(!m_wndSplitterWnd2.CreateStatic(&m_wn...

question re: read icon
When I open my inbox in the morning the read icon appears beside each message. Throughout the day as I retrieve mail, some of the messages will have the read icon and some of them will have the unopened icon. Why do some of the messages appear to have been read if I have never opened them or even clicked on them? Thanx in advance for your help ...

AutoCorrect #2
Can 'AutoCorrect' listings be transported from an old computer to a new one? I have hundreds of 'auto-corrections' that would take me all day to redo. Copying the dictionary does not seem to do it. Robert, it is stored as a .ACL file ,do a search for *.acl and copy it to the new computer -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 97 & 2000 ** remove news from my email address to reply by email ** "robert gerald" <...

CRM 1.2 subnet change
I am moving my CRM servers to a different site for DR, and I need to know if I need to do anything to the server besides updating the DNS records with the new IPs. Anyone have any experience with this? Thanks ...

a couple of spell check questions
Neither of these is more than an inconvenience but... How can I prevent spell check from checking the subject text in replies? If I'm replying to an email, I don't want to correct or change the subject. Sometime when I create a new email, I get a message "Spell checking has been halted. Do you want to send anyway" when I didn't halt the spell checker. Is this a known problem? Hi Duane, Q1 Check "The original text ..." box. Q2 - Tools; Options; Spelling. UNcheck "Always check spelling before sending" and Check spelling in current in...

duplicate #2
I have spreadsheet that contains about 200,000 records. Many of them are duplicated. How can I sort them out so that I can only have one of each? Metalteck wrote: || I have spreadsheet that contains about 200,000 records. Many of them || are duplicated. How can I sort them out so that I can only have one || of each? have a look here: http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk ASAP Utilities has a feature that does this nicely.......... Free at www.asap-utilities.com Vaya...