Count the occurence of multiple (4 conditions). - Need urgent help

Hi,

I am unable to get the correct count using the formula :

=SUM(IF(A1:A999="       
A",IF(D1:D999="D>d",IF(F1:F999="1",IF(E1:E999="1",1,0)))))

I am trying to get no of records with 
"       A" values in Column A,
"D>d" values in Column D,
"1" values in Column F,
"1" values in Column E

and Sum all the counts- after applying the nested filters.
 

Can someone help me in debugging the same? 
Will appreciate early response!

Thanks in advance,

0
CT (8)
5/2/2005 12:34:03 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
429 Views

Similar Articles

[PageSpeed] 54

Try this:

=SUMPRODUCT((A1:A999="A")*(D1:D999="D>d")*(E1:E999=1)*(F1:F999=1))

I made an assumption that the 1's in Column E and F were real numbers, and
didn't need the quotes.

If I guessed wrong, and they are text, just add the quotation marks.


-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"CT" <CT@discussions.microsoft.com> wrote in message
news:D3C85D07-7C42-4751-A488-D069138189A8@microsoft.com...
> Hi,
>
> I am unable to get the correct count using the formula :
>
> =SUM(IF(A1:A999="
> A",IF(D1:D999="D>d",IF(F1:F999="1",IF(E1:E999="1",1,0)))))
>
> I am trying to get no of records with
> "       A" values in Column A,
> "D>d" values in Column D,
> "1" values in Column F,
> "1" values in Column E
>
> and Sum all the counts- after applying the nested filters.
>
>
> Can someone help me in debugging the same?
> Will appreciate early response!
>
> Thanks in advance,
>

0
ragdyer1 (4060)
5/2/2005 1:43:04 AM
You would have to array enter (Ctrl-Shift-Enter) this formula for it to 
work.  A simpler approach would use the fact that you can coerce TRUE 
into 1 and FALSE into 0, so that the following formula should work and 
does not require array entry:

=SUMPRODUCT((A1:A999="A")*(D1:D999="D>d")*(F1:F999="1")*(E1:E999="1"))

Jerry

CT wrote:

> Hi,
> 
> I am unable to get the correct count using the formula :
> 
> =SUM(IF(A1:A999="       
> A",IF(D1:D999="D>d",IF(F1:F999="1",IF(E1:E999="1",1,0)))))
> 
> I am trying to get no of records with 
> "       A" values in Column A,
> "D>d" values in Column D,
> "1" values in Column F,
> "1" values in Column E
> 
> and Sum all the counts- after applying the nested filters.
>  
> 
> Can someone help me in debugging the same? 
> Will appreciate early response!
> 
> Thanks in advance,
> 
> 

0
post_a_reply (1395)
5/2/2005 2:00:15 AM
No need to array enter it

-- 
Regards,

Peo Sjoblom


"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message 
news:427589AF.3050700@no_e-mail.com...
> You would have to array enter (Ctrl-Shift-Enter) this formula for it to 
> work.  A simpler approach would use the fact that you can coerce TRUE into 
> 1 and FALSE into 0, so that the following formula should work and does not 
> require array entry:
>
> =SUMPRODUCT((A1:A999="A")*(D1:D999="D>d")*(F1:F999="1")*(E1:E999="1"))
>
> Jerry
>
> CT wrote:
>
>> Hi,
>>
>> I am unable to get the correct count using the formula :
>>
>> =SUM(IF(A1:A999=" 
>> A",IF(D1:D999="D>d",IF(F1:F999="1",IF(E1:E999="1",1,0)))))
>>
>> I am trying to get no of records with "       A" values in Column A,
>> "D>d" values in Column D,
>> "1" values in Column F,
>> "1" values in Column E
>>
>> and Sum all the counts- after applying the nested filters.
>>  Can someone help me in debugging the same? Will appreciate early 
>> response!
>>
>> Thanks in advance,
>>
>>
> 

0
terre081 (3244)
5/2/2005 2:00:19 AM
The OP's formula

=SUM(IF(A1:A999="A",IF(D1:D999="D>d",IF(F1:F999="1",IF(E1:E999="1",1,0)))))

does require array entry to work, as I tried to say.  I presume lack of 
array entry is why CT could not get it to work.

My alternative formula

=SUMPRODUCT((A1:A999="A")*(D1:D999="D>d")*(F1:F999="1")*(E1:E999="1"))

does not require array entry, as I did say.

On rereading my previous reply, the object referred to by "this formula" 
in my first sentence is not clear, and should probably have read "your 
formula".

Jerry

Peo Sjoblom wrote:

> No need to array enter it

0
post_a_reply (1395)
5/2/2005 11:42:16 AM
I see that now, thanks for the clarification

-- 
Regards,

Peo Sjoblom


"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message 
news:42761218.3080906@no_e-mail.com...
> The OP's formula
>
> =SUM(IF(A1:A999="A",IF(D1:D999="D>d",IF(F1:F999="1",IF(E1:E999="1",1,0)))))
>
> does require array entry to work, as I tried to say.  I presume lack of 
> array entry is why CT could not get it to work.
>
> My alternative formula
>
> =SUMPRODUCT((A1:A999="A")*(D1:D999="D>d")*(F1:F999="1")*(E1:E999="1"))
>
> does not require array entry, as I did say.
>
> On rereading my previous reply, the object referred to by "this formula" 
> in my first sentence is not clear, and should probably have read "your 
> formula".
>
> Jerry
>
> Peo Sjoblom wrote:
>
>> No need to array enter it
> 

0
terre081 (3244)
5/3/2005 3:47:27 AM
Reply:

Similar Artilces:

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

crm consultant needed asap
i am looking for a crm consultant who has a lot of experience with form customization, crm implimentation, heavy work flow, and activities. ideally someone in arizona but not required. telecommute will be considered for the right person. looking for someone for a possible 2 month (i am guessing here....) project. if you are available and have this experience please email me at j-e-f-f@mag-en-ta-tech.c-o-m (remove the -). we are looking for someone to start like next week at the latest (this message was posted 08-08-2004). This message was posted 8/4, not 8/8. And I have some swamp land in f...

multiple email identities
HELP! My husband and I share the same computer and we have our email come to our inbox through Microsoft Outlook. However it is merging our accounts- how do we seperate into 2 identities so we each get our own mail! It is so frustrating! Please help! Thanks Kat Outlook does not use Identities - it uses profiles. See this page for more information: http://www.slipstick.com/outlook/share.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. After s...

product key #4
I have a product key for microsoft office which I purchased for my pc several months back. I am now trying to use that same product key to register office on my mac (office was pre-installed). Where do I enter in this product key in the mac office trial software in order for me to get full access? In article <1190872643.026720.79220@w3g2000hsg.googlegroups.com>, jaime825@gmail.com wrote: > I have a product key for microsoft office which I purchased for my pc > several months back. I am now trying to use that same product key to > register office on my mac (office was pre...

Multiple domains on one Exchange server / All but one domain shows up as spam #2
I am sorry if this is on the board somewhere but I have been searching forever and havn't found a hint yet. I have multiple domains setup on my exchange server. Lets say abc.com, def.com, and hij.com. We only send email with @abc.com but can receive from any domain name. Recently we merged with another company and we brought back their email to our server, lets say xyz.com. Now I configured a new recipient on Exchange 2003, so I now have two policies. The first policy has the three original domains in it with @abc.com as the primary domain. In the second policy I have configured xyz.com t...

Congratulations! This has been a MEB-free newsgroup for 4 weeks.
We all deserve a pat on the back. This has been a MEB-free workplace for over 4 weeks - practically the entire month of June. As Microsoft turns off it's usenet server and removes this newsgroup from it's server, we have seen the last of Maurice Edward Brahier and as this group continues to exist outside of Microsoft we will never again see him post to this group. The fact that he has been a fraud and not a real user of Windows 98 for many months if not years probably has something to do with that. His incoherent and rambling posts, written in a particularly obtuse form ...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the current form field. This bit of code is attached to the GotFocus event of all fields and the Enter event of all subforms: gxCurrentField = Me.ActiveControl.Name However it doesn't work properly when changing records in a subform. My parent form contains two subforms in a many-to-many relationship. The above variable usually ends up containing the name of the first field in the second subform when switching records in the first subform. How to correctly code this? Or is there some native variable I'm not aware of? I...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

GETPIVOTDATA data_field help
When I enter a reference to a cell, eg A2, as the value for the data_field item in the GETPIVOTDATA I get a #REF error. However, when I instead type the value of A2, eg 2003, manually into the GETPIVOTDATA formula, it works. Can anyone please help--I want this formula to vary based on what is in A2. Cheers This function is so variable in action, and difficult to handle, that have given it up in favour of VLOOKUP. In fact, having no problem i use with pivot tables -- Message posted from http://www.ExcelForum.com Try copying the headings from the pivot table, and pasting them into the ce...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

Removing spaces from text #4
I'm in excel and i have a bunch of text data that has an extra space at the end of the text on the right hand side for each cell. Is there any easy way to remove this space? Use the TRIM() function. -- Kind regards, Niek Otten "lj" <lj@spu.edu> wrote in message news:1144876429.220961.309040@j33g2000cwa.googlegroups.com... > I'm in excel and i have a bunch of text data that has an extra space at > the end of the text on the right hand side for each cell. Is there any > easy way to remove this space? > I tried using that function but the results st...

Help!!!!
I am running Exchange 5.5 on NT 4.0 sp6a. Norton Anti- virus deleted my edb.log file. I have done an on-line restore with Arcserve 2000 and now my directory service won't start. getting 1166 internal processing error. I have been to support and could find anything that helped. Thanks in advance You may want to run eseutil /mh <drive and path to your dir.edb and include the dir.edb > c:\edb.dmp. Find this file and see if your dir is also inconsistent. If so you will need to restore your dir. -- Nan Bennett (MSFT) Microsoft Exchange Support Please do not send e-mail...

Status Bar #4
Hi - can anyone tell me what the "official" name is for the facility on the Excel Status Bar which allows you to select a range of cells and view the Sum, Average etc of those cells? Thanks. It's the AutoCalculate feature: AutoCalculate feature is working even if that feature is turned off http://support.microsoft.com/default.aspx?id=325703 XL7: CTRL+Click Selected Cell Changes AutoCalculate Value http://support.microsoft.com/default.aspx?id=133050 Alex wrote: > Hi - can anyone tell me what the "official" name is for the facility on the > Excel St...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

OLE Notes #4
Windows Server 2000 SP4 MS SQL Server 2000 SP4 MS Dynamics 9.0 SP3 When a note is entered on a vendor card (Cards, Purchasing, Vendor, Note icon) a dialog box titled OLE Pathname appears. The purpose is a shared notes location. If the user clicks ok or cancel without entering a path, where is the note stored? I have two vendors that have the exact same note, entered at the exact same time, by the same user. In theory this should not be possible. The OLE path is not specified in the dex.ini file. -- Thanks Titan Did you not find the note in SY03900 table on the company? The vendor c...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

creating template for multiple emails
How or can one create an email template (same subject line and message)for multiple emails (approx 100's)? Needing to shorten the time it takes sending 100's of individual emails (with the same subject line and same message) to sales staff on a weekly (some times daily) basis. Company does not want sales force using other sales persons email addresses (they are contractors). Try using a Distriburtion List, send one message, it goes to all of them, or get the message ready, put your address in the TO: field, and put everyone else in the BCC: field?? That way no one can see wher...

calendar for multiple time zone users: all day events
Users in different time zones post absences and meetings to a shared company calendar. When an all day event is scheduled in one time zone, it shows as spanning two days for other time zone users. How do we make this work properly? For the others it does intrude on a second day. BossLady wrote: > Users in different time zones post absences and meetings to a shared company > calendar. When an all day event is scheduled in one time zone, it shows as > spanning two days for other time zone users. How do we make this work > properly? Until you upgrade to Outlook...

I really need help with a formview problem
I have a formview that I place a toolbar onto. In my OnInitialUpdate(), after I create the toolbar, I say GetParentFrame()->RecalcLayout(); ResizeParentToFit(FALSE); ResizeParentToFit(TRUE); The formview is still not sized properly. It looks like the problem is the toolbar on the top of the formview. It is not being taken into account in sizing the formview. The difference at the bottom of the form that is not displayed is the size of the toolbar. Any suggestions? Should work if your toolbar was created (and docked) back in your CMainFrame::OnCreate() method. ----- William Gower ...

Bank One Download is not working
We are trying to download transaction data directly from Bank One and the file is not being recognized by Money - i.e. the Bank One website states that the download is complete, but Money does not recognize it. Note that a file is being saved on our hard drive but we can't figure out how to read it. It is entitled mnyimprt.exe-(numerous numbers and letters follow). In microsoft.public.money, Miki wrote: >We are trying to download transaction data directly from >Bank One and the file is not being recognized by Money - >i.e. the Bank One website states that the downloa...