Need help with a formula!

Hello,
I have a large worksheet with data (columns "A", "B", "C", "D" "E", "F" & 
"G").
(I'm using only a few records for reference).

Here are the columns:

Quote	Quote	Quote	STD	STD	ENG	ENG
Type	Number	Status	OPEN	CLOSED	OPEN	CLOSED
STD	Q090001	CLOSED	formula	formula	formula	formula
ENG	Q090002	CLOSED
STD	Q090003	CLOSED
STD	Q090004	OPEN
STD	Q090005	CLOSED
ENG	Q090006	CLOSED
ENG	Q090007	CLOSED
STD	Q090008	CLOSED
STD	Q090009	OPEN
ENG	Q100001	OPEN
STD	Q100002	CLOSED
STD	Q100003	CLOSED
ENG	Q100004	OPEN
STD	Q100005	OPEN
STD	Q100006	OPEN

I need to count how many "STD" & "ENG" quotes are "OPEN" & "CLOSED"
I need a formula in columns "D", "E", "F" & "G".

Is there a formula for this?

Help please!!!

Thank you,
Cesar Urquidi
0
Utf
3/9/2010 7:21:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
614 Views

Similar Articles

[PageSpeed] 28

Hi,

=SUMPRODUCT((A14:A17="STD")*(B14:B17="Open"))
=SUMPRODUCT((A14:A17="STD")*(B14:B17="Closed"))
=SUMPRODUCT((A14:A17="ENG")*(B14:B17="Open"))
=SUMPRODUCT((A14:A17="ENG")*(B14:B17="Closed"))

change range to meet your needs


"Cesar Urquidi" wrote:

> Hello,
> I have a large worksheet with data (columns "A", "B", "C", "D" "E", "F" & 
> "G").
> (I'm using only a few records for reference).
> 
> Here are the columns:
> 
> Quote	Quote	Quote	STD	STD	ENG	ENG
> Type	Number	Status	OPEN	CLOSED	OPEN	CLOSED
> STD	Q090001	CLOSED	formula	formula	formula	formula
> ENG	Q090002	CLOSED
> STD	Q090003	CLOSED
> STD	Q090004	OPEN
> STD	Q090005	CLOSED
> ENG	Q090006	CLOSED
> ENG	Q090007	CLOSED
> STD	Q090008	CLOSED
> STD	Q090009	OPEN
> ENG	Q100001	OPEN
> STD	Q100002	CLOSED
> STD	Q100003	CLOSED
> ENG	Q100004	OPEN
> STD	Q100005	OPEN
> STD	Q100006	OPEN
> 
> I need to count how many "STD" & "ENG" quotes are "OPEN" & "CLOSED"
> I need a formula in columns "D", "E", "F" & "G".
> 
> Is there a formula for this?
> 
> Help please!!!
> 
> Thank you,
> Cesar Urquidi
0
Utf
3/9/2010 7:29:03 PM
Hello Eduardo,
It worked just like I needed...
Thank you very much Sr.!!!

Cesar Urquidi

"Eduardo" wrote:

> Hi,
> 
> =SUMPRODUCT((A14:A17="STD")*(B14:B17="Open"))
> =SUMPRODUCT((A14:A17="STD")*(B14:B17="Closed"))
> =SUMPRODUCT((A14:A17="ENG")*(B14:B17="Open"))
> =SUMPRODUCT((A14:A17="ENG")*(B14:B17="Closed"))
> 
> change range to meet your needs
> 
> 
> "Cesar Urquidi" wrote:
> 
> > Hello,
> > I have a large worksheet with data (columns "A", "B", "C", "D" "E", "F" & 
> > "G").
> > (I'm using only a few records for reference).
> > 
> > Here are the columns:
> > 
> > Quote	Quote	Quote	STD	STD	ENG	ENG
> > Type	Number	Status	OPEN	CLOSED	OPEN	CLOSED
> > STD	Q090001	CLOSED	formula	formula	formula	formula
> > ENG	Q090002	CLOSED
> > STD	Q090003	CLOSED
> > STD	Q090004	OPEN
> > STD	Q090005	CLOSED
> > ENG	Q090006	CLOSED
> > ENG	Q090007	CLOSED
> > STD	Q090008	CLOSED
> > STD	Q090009	OPEN
> > ENG	Q100001	OPEN
> > STD	Q100002	CLOSED
> > STD	Q100003	CLOSED
> > ENG	Q100004	OPEN
> > STD	Q100005	OPEN
> > STD	Q100006	OPEN
> > 
> > I need to count how many "STD" & "ENG" quotes are "OPEN" & "CLOSED"
> > I need a formula in columns "D", "E", "F" & "G".
> > 
> > Is there a formula for this?
> > 
> > Help please!!!
> > 
> > Thank you,
> > Cesar Urquidi
0
Utf
3/9/2010 7:57:02 PM
your welcome, thanks for your feedback

"Cesar Urquidi" wrote:

> Hello Eduardo,
> It worked just like I needed...
> Thank you very much Sr.!!!
> 
> Cesar Urquidi
> 
> "Eduardo" wrote:
> 
> > Hi,
> > 
> > =SUMPRODUCT((A14:A17="STD")*(B14:B17="Open"))
> > =SUMPRODUCT((A14:A17="STD")*(B14:B17="Closed"))
> > =SUMPRODUCT((A14:A17="ENG")*(B14:B17="Open"))
> > =SUMPRODUCT((A14:A17="ENG")*(B14:B17="Closed"))
> > 
> > change range to meet your needs
> > 
> > 
> > "Cesar Urquidi" wrote:
> > 
> > > Hello,
> > > I have a large worksheet with data (columns "A", "B", "C", "D" "E", "F" & 
> > > "G").
> > > (I'm using only a few records for reference).
> > > 
> > > Here are the columns:
> > > 
> > > Quote	Quote	Quote	STD	STD	ENG	ENG
> > > Type	Number	Status	OPEN	CLOSED	OPEN	CLOSED
> > > STD	Q090001	CLOSED	formula	formula	formula	formula
> > > ENG	Q090002	CLOSED
> > > STD	Q090003	CLOSED
> > > STD	Q090004	OPEN
> > > STD	Q090005	CLOSED
> > > ENG	Q090006	CLOSED
> > > ENG	Q090007	CLOSED
> > > STD	Q090008	CLOSED
> > > STD	Q090009	OPEN
> > > ENG	Q100001	OPEN
> > > STD	Q100002	CLOSED
> > > STD	Q100003	CLOSED
> > > ENG	Q100004	OPEN
> > > STD	Q100005	OPEN
> > > STD	Q100006	OPEN
> > > 
> > > I need to count how many "STD" & "ENG" quotes are "OPEN" & "CLOSED"
> > > I need a formula in columns "D", "E", "F" & "G".
> > > 
> > > Is there a formula for this?
> > > 
> > > Help please!!!
> > > 
> > > Thank you,
> > > Cesar Urquidi
0
Utf
3/9/2010 7:59:01 PM
Reply:

Similar Artilces:

Need advice on Single Doc, Multi View framework
Hi guys, I'm about to start on a quoting application. What I want is for only one quote (document) to be open at a time. However a quote can have one or more items belonging to it. So what I'd like to do is have a CDialogBar docked at the side of my app's main window which contains the details about the quote (date, client, etc) and for each quote item I'd have a CFormView (a must as I require scrolling support). The application must also have print/print preview support (Not of what is in the CFormViews but of the document as a whole which doesn't have a view). So basical...

Help on subscript out of range error (VB6/VBA)
Hi, I want to work with multiple excel workbooks en worksheets using a stand alone VB6 application. 1. First I open and close the workbooks and populate a listbox with sheetnames. 2. Then I select some sheets and fill an Array. (This array contains sheetnames from different workbooks) 3. The last step is to open and close the workbooks again and loop (For Each) through the sheets to get the information I need using the above mentioned array. The only problem is that I receive an "Subscript out of Range" error. This error occurs because I'm attempting tot access a workshee...

HELP! **Transfer orders to invoices error
I need some serious help. We just upgraded from GP 7.5 to GP 8.0 and now I am unable to transfer orders to invoices. I keep getting an error that says "Order ORD1555 has no line items to transfer to Invoice INV144408." Order# 1555 does have about 10 line items, which I can view on the screen. I did go in and run the "Check Links" function, but it hasn't done anything. This is our busiest time of year and a really bad time for something like this to happen. Can someone please give me some advice on how to fix this? if this is just one order, why not cancel...

Any Help documentation for Exchange 2007?
E2k7' help button gives 'Cannot display Help' Technical details: HC not found. (MS.OSSADMIN.manifest , 1033) Did I miss something? Not all the help is completed in the Beta 2 release. "Prem" <premmetje@zonnet.nl> wrote in message news:1161634519.758601.285030@i42g2000cwa.googlegroups.com... > E2k7' help button gives 'Cannot display Help' Technical details: HC not > found. (MS.OSSADMIN.manifest , 1033) > Did I miss something? > ...

Pls, help a beginner(MFC)
Could anybody tell me what is the future of MFC? I never used that library. I just got some skills in pure C++. But some knowledgeable people are saying it's not a good idea to study MFC, because "it's going to be obsolete" and replaced by .Net. They advise me to study C# because as they say "studying MFC will take you quite a lot of time by which Longhorn will be released and all coding will be done in managed way"... Well, I really don't know which direction to elect According to me :) MFC never be obsolete and it would be safe...

Help
My Regional Manager insists on things being perfect - need assistance with this problem. Working in Governemtn department - we have many users with unusal names and surnames - not common to standard dictionaries. My Rm wants to forward emails received with these peoples names in the original message - without outlook spell checking each of these names before sending. I have set up the email options under spelling - to ignore original message when forwarding - which it does but still spell checks the original message's recipients before sending. How do I set this up to exclude the n...

Distribution list help needed
We created a distribution list. Internal active directory users are able to send to the list. Internet e-mail senders are not able to send to the list. The mail is not bouncing back to the Internet e-mail users. The messages are just not getting to the inbox of the members of the distribution list. For example our domain is abc.net. The list we created is questions@abc.net. We have four users in the list. When a user logged into our Active Directory sends a mail to questions@abc.net the mail is successfully sent to the inboxes of all of the users in the list. When the e-mail ...

Uploading Back to the Yahoo server...Please Help
Dear Member of this Help Forum; please help..please help me because i have lost all my important emails and i am so depressed...so sad....please Help....i HAVE SEARCHED THIS FROUM BUT NO ONE HAD THIS PROBLEM BEFORE.... My story and what i have done : I have a "Yahoo Small Business Account" with domain name and hosting, and 3 email address from that account. i used to use NO EMAIL CLIENT LIKE OUTLOOK at all and i used to save all my emails on yahoo servers and using yahoo normal email website to read, send and save all my emails...untill tonight! tonight i set up outlook 200...

.pst file is maxed out - HELP
Apparently I have reached the maximum size of my outlook.pst file (see attached error message). I have tried deleting items but nothing I do seems to have any effect and I continue to get the attached error message. Therefore the eMail part of Outlook is disabled. Anyone got any pointers? XP Pro SP-2/Outlook 2003. Already using .pst format for database. Thanks, No image attached. Post a link, rather than an attachment. See http://www.slipstick.com/problems/repair2gbpst.htm=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/o...

Fixing a formula
I have the following equation, it is looking up in another worksheet an pulling through information when it finds a match in second file fro cell d in first file. =VLOOKUP(D10,'C:\New Code Se up\[gcodenewsetupcopy.xls]Sheet1'!C4:O81,4,FALSE) This is working but when i copy the equation down a few rows th equation changes to this and it should still be looking at Sheet 1 C to O81. =VLOOKUP(D14,'C:\New Code Se up\[gcodenewsetupcopy.xls]Sheet1'!C8:O85,4,FALSE) I know i need to fix part of the equation but don't know how or whic bit. Please help -- Boethius -----------...

Replacing part of a formula
Currently I am working on a project that uses formulas to referenc another sheet in the workbook. I plan to use the newly created shee monthly. However I am in need of changing part of the cell formula. Example one cell says =Nov!D12 and I am needing to change it to rea =Dec!D12 or =Jan!D12 etc.... Any help -- Message posted from http://www.ExcelForum.com One way is to use the INDIRECT function when creating your formulas, eg:- Assuming your month was in say A3 then =INDIRECT(A3&"!A1") will give you the data from cell A1 on whatever sheet is listed in A3. Another option i...

Re: Can anyone help me with the IF function
Hello I'm trying to help my wife with simplifying her scheduling o employees. She uses 11 different time periods (1-11) some are worth the same amount of hours others are more or less. I can us seven functions to describe the period and the value of hours it is worth, but when I consolidate the functions i says the formula is invalid. I'm sure it can be fixed by one of you excel gurus! Here are the functions IF(B13<4,"7.75") IF(B13=4,"7.25") IF(B13=5,"6.25") IF(B13=6,"7.75") IF(B13=7,"6.75") IF(B13=8,"6.25") IF(B...

Help
Hi, i need to define a server side rule, which will copy attachments from received mails locally on PC/server. Any idea, scripts, anything? Tnx! Seems like ftp would be more appropriate than e-mail. But if you insist http://support.microsoft.com/search/default.aspx?spid=1773&query=event+sink+attachments&catalog=LCID%3D1033&pwt=false&title=false&kt=ALL&mdt=0&comm=1&ast=1&ast=2&ast=3&mode=a&x=18&y=11 HrVOjE wrote: > Hi, > > i need to define a server side rule, which will copy attachments from > received mails locally on PC/ser...

Formula Help #23
If I have a number in cell A1 like 5 and another number in cell A3 like 6 I want the number in A7 to show -1 and if cell A3 was 4 to read +1 and if the numbers are 2 away from A1 then the numbers in cell A7 would be -2 or +2 depending on the number in cell A3. All help is appreciated. Thanks Karl You must be kidding! =A1-A3 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Karl" <Karl@discussions.microsoft.com> wrote in message news:21FB7D9E-DC79-42...

Need to Load Publisher
I have Publisher 98 CD and lost the jewel case that had the CD Key Value. I have anew computer and just needs the functionality of Publisher 98. How can I get the 11-disgit key value? I have a valid product ID when I loded the software on the old computer Help?? MIke http://www.phroogle.com/index.php?str=p&st=4520 -- Brian Kvalheim Microsoft Office Publisher MVP Official Publisher MVP Site: http://www.kvalheim.org This posting is provided "AS IS" with no warranties, and confers no rights. "Mike" <mbenya@adelphia.net> wrote in message news:027d01c3c...

Percentage formula in Excel spreadsheet
Hi, In my spreadsheet cell E14 adds up E2 - E14 and cell C14 adds up C2 - C14. In G14 I have put a formula to show the difference between E14 and C14 as a Percentage ( formula for G14 is =SUM(E14-C14)/ABS(E14) ). When the Formula for G14 looks at E14 and C14 all it sees is a SUM formula to add up the column. Does anyone know how to solve this problem. Many thanks for looking. Rick Not sure I understand the problem; tell us what answer or error you ar getting. Also, don't you mean you are summing C2:C*13* in C14 and E2:E*13* i E14? Oh, and you can get rid of the Sum - this ...

Help Wanted...
I am looking to fill an entry level position within our company. We are a managed services provider in Dallas and have an immediate need for an entry level technician. If you know anyone who has some basic technical knowledge or aptitude and a willingness to learn, please forward this posting to them. I am primarily looking for someone who is capable and willing to learn more so than someone who has a lot of training and/or experience. This is an entry level position, but there will be a lot of opportunity for training and advancement. This position is full-time (contract to hire) and is avail...

I need help with My IF Statement....
I'm a college student and we are doing an assignment. I made it all th way to the end without any problems and now I'm stuck. The part I' stuck on involves using an IF Statement to determine whether or not bonus should be applied based on sales made. This will make much mor sense once you look at my workbook. I've got everything setup I jus need some help on where to put my numbers and formulas. If you think you can help me, reply to this thread or email me a ashbyb14@gmail.com. I can email you my workbook and instructions of what I'm suppose to d. Thank -- ash...

Using a txt string in formulae
Right this is an anoying little beggar but probably simple and im failing to see the wood from the trees so to speak ! right i have a workbook with multiple sheets and a front sheet the front sheet reports results of counts from the other sheets using =COUNT(sheet1!A:A) and =COUNTIF(Sheet1E:E,"Neg room") and other similair formula my question is this In column A i have the name of the sheet so I.E. A1=sheet1 which hyperlinks to the named sheet and columb B has the first formulae and so on and so on! my question is is there a way i can add the Word name of the sheet the Count form...

email needs to relate to multiple cases
Hello, We often get one email that prompts the creation of multiple cases being created. I would like a way to clone/copy the email and then be able to relate it to many cases. Any suggestions? Thx heather ...

HELP!! Repeating Text over multiple Pages
I created a template containing a title, picture, map, and text box. The text box in particular contains information pertaining to a specific location. I created a 131 individual files using that template and everything came out looking great (thanks Microsoft). I had to create 131 individuals files because we needed to have it backed up for our server at work. The problem I am having now is when I attempted to combine each of 131 files by copying and pasting them into 1 publisher file containing 131 pages. The title, picture and map all preserve their own information. HOWEVER, the in...

Icon guru needed
Hi, does someone know how windows decide to display a icon holding several resolutions. I am compiling an exe with the icon provided by a designer and when I am in small list view it's ok but in large view the masked icon is displayed. Is there any recommandations about ico resources ? My icon has 4 images : 16x16x32 normal icon 16x16x32 normal icon 32x32x32 masked icon (greyscale) 32x32x32 normal icon 32 is not a possible color depth. You can have 4-bit (16-color), 8-bit (256-color) and 24-bit (16,77,216-color), but not 32-bit or 32-color. What do you mean by "the masked icon ...

need ide controller in laptop with only sata
I need to get some kind of PC card that will look like a real IDE controller and use the same addresses as either the primary or secondary channel on a PC with a built-in ide controller. I tried to use a PC card that was an eSATA controller connected to a SATA->PATA bridge, but a device connected to the bridge showed up in Device Manager as a SCSI disk. "richk" <nospam@nospam.com> wrote in message news:ezAbdWOvKHA.3896@TK2MSFTNGP02.phx.gbl... >I need to get some kind of PC card that will look like a real IDE >controller and use the same addresses as ...

Formula Help #31
I have 12 columns in a spreadsheet. One for every month of the year. As each month is completed the data for that month goes in that column. I need a fromula in a specific cell that will return the value of the most recent column (month) that is filled in. So in July it returns the data for July but will return the data for August when a value is placed in that cell. Any suggestions? Thank you. Steve Hubbard Steve, To return the right-most value from row 2 (Assuming your data start starts in column A and extends to the right) use the array formula (entered with Ctrl-Shift-Enter): =IND...

Email templates NEED to allow customized entity fields
How can CRM not allow for customized fields from customized entities to be placed into email templates. If ACT can figure out how to do it, CRM should have it also. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community...