sumif formula required

Hello

I have only basic knowledge of excel and need a formula to extrac
information.

A                          B                               C
Client                   Process                      Sqm
CEK                      Fibre                           100
RDF                      Gloss                            200
ERF                      Emul                             150
CEK                      Fibre                             100

What formula should I use to extract all CEK clients who purchase th
fibre process giving total sqm.

I would be extremely grateful to anyone who can help me.

Regards

Jennife

--
Message posted from http://www.ExcelForum.com

0
1/7/2004 9:06:53 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
844 Views

Similar Articles

[PageSpeed] 20

Use the SumProduct formula (it handles more than one condition). You
cannot use full column references (A:A), however, in SUMPRODUCT.

=SUMPRODUCT(($A$1:$A$300="CEK")*($B$1:$B$300="Fibre"),$C$1:$C300)


---
Message posted from http://www.ExcelForum.com/

0
1/7/2004 9:13:26 PM
Hi

Thank you very much.  This will save me lots of time.

Regards

Jennife

--
Message posted from http://www.ExcelForum.com

0
1/7/2004 9:20:02 PM
You can either:-

Use Data Filter / Autofilter in conjunction with the SUBTOTAL function, which
will Sum only the visible rows after filtering,

or

You can use something like SUMPRODUCT eg:-

=SUMPRODUCT((RngA="CEK")*(RngB="Fibre")*(RngC))

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"jenniferThomson >" <<jenniferThomson.zojpf@excelforum-nospam.com> wrote in
message news:jenniferThomson.zojpf@excelforum-nospam.com...
> Hello
>
> I have only basic knowledge of excel and need a formula to extract
> information.
>
> A                          B                               C
> Client                   Process                      Sqm
> CEK                      Fibre                           100
> RDF                      Gloss                            200
> ERF                      Emul                             150
> CEK                      Fibre                             100
>
> What formula should I use to extract all CEK clients who purchase the
> fibre process giving total sqm.
>
> I would be extremely grateful to anyone who can help me.
>
> Regards
>
> Jennifer
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004


0
ken.wright (2489)
1/7/2004 9:20:37 PM
Reply:

Similar Artilces:

Formula Check... Please
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C61A25.29EC1E10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I didn't receive a response on my last post so I worked on my problem = some and came up with some results. Could someone look over my formulas = and tell me if I can achieve this same result any easier? Here is a sample of the worksheet: D E F G H I J K L M N O=20 22 Sun Mon Tue Wed Thu Fri Sat Reg OT x1.5 OTx 2 Rate Total=20 23 off 12 11 11 11 11 off 40 15 1 10.00 $645.00=20 ...

Can I use the NOW() function in a formula?
I have a cell B1 that has NOW() time and another cell B2 that has NOW() date. I am trying to have data from another cell F1 brought in to the destination cell B3, when a given date and time occur. =(IF(AND(B1="23:00:00",B2="12/03/2009"),F1," ") This formulas does not work. The Clock is continuously active / always changing. Any suggestions would be appreciated. Thanks. The NOW() function returns both a date and a time. Even if you format the cell to display only date/time, all the data is still retained. NOte that if you did want just the d...

Help with formula please.
I posted this a couple of days ago but doesnt seem to have appeared so I hope this is not a duplicate. Original formula from previous post... =(("12:00"-MAX(C11,--"7:00"))*0.3+(MAX("23:00",C11)-MAX(C11,"12:00"))*0.4+(C12-MIN(C12,"23:00"))*0.5)*24 I have changed the formula to =(("12:00"-MAX(C11,--"7:00"))*0.3+(MAX("23:00",C11)-MAX(C11,"12:00"))*0.4+(C12-MIN(C12,"23:00"))*0.5)*C13 As a test I have put in aircon running for 1 hour between 10-11 am So this should return a value of .30 It is ...

Copy and Paste without copying Formulas
I have created a workbook with several sheets. The first sheet consists of a master list of 8000 numbers. The second sheet is where I paste a smaller list of numbers. The workbook performs a search and returns numbers that match on the third sheet. The third sheet also has formulas. I want to be able to copy and paste the resulting info on the third sheet to another workbook without copying any of the formulas......I just want the info. Any suggestions?? Ken Copy>Paste Special>Values>OK>Esc. Gord Dibben Excel MVP On Thu, 18 Nov 2004 14:21:13 -0800, "Ken"...

Selecting X cells based on answer to formula
Hi, I am trying to put together a spreadsheet that performs a couple of tasks. The first is basic resourcing which i am ok with, sort of how many heads and how many hours available means X cases will be done. The next thing I need to do is use the X cases result to identify from a list the oldest date and work through sequentially X dates in that column, which will not be consecutive, and tell me what the date of that case is. So if there were 10 cases done I would like one cell saying the oldest case worked on was - first date in column. And following doing the 10 cases I'd lik...

how do I insert a filename into a formula from another cell?
I have a spreadsheet with a filename in a cell. I would like to reference that filename in a formula. I can't seem to get it to work. "+cell number" doesn't work. Any suggestions? You would normally use INDIRECT to do this, along the lines of: =INDIRECT("["&A1&"]Sheet1!C2") where A1 contains your filename (with the .xls extension) and you are trying to return data from C2 on Sheet1 of that file. However, INDIRECT will only work with files that are open, so you would have to have the file open for this to work. Hope this helps. Pete On...

Array formula
{=SUM((F45=10)*(H45={1,2,3})*{20,10,5})+((F45=20)*(H45={1, 2,3})*{42,21,11})} This formula works fine, up until the separated part, then it just does nothing(I put the spaces in just to clarify the problem area for this discussion). No matter what order put the arguments in, it only works up to the same point. Is this just a case of to much info? How do I extend this formula (I need it to be even longer the above example). thanks. Hi Atom, Try replacing SUM by SUMPRODUCT and do a normal completion with just ENTER since it is not an array formula Bernard "atom" <hmm@hmm.com...

Sumif
If this is a sample of my spreadsheet, how do I calculate so that the results will look at both col A and B and sum the total in col C for each variable in col. A. In other words what is the total amount for A for Jan, Mar... Col A Col B Col C Col D a Mar 254 850 a Jan 266 125 c Feb 987 121 c Jan 885 151 a Mar 352 850 b Jan 558 454 Thanks, Anat A pivot table would give you the desired results. -- Damon Longworth Don't miss out on the 2005 Excel User Conference Sept 16th and 17th Stockyards Hotel - Ft. Worth, Texas www.ExcelUserConfere...

can i create formula giving totals based on financial & text info
Am i able to create a formula that gives me monetray totals for expenditure on hotels, split into totals for 6 varying business sectors? ...

Ability to create formula to calculate benefit or deduction code
Would like to use a benefit or deduction based on a formula to calculate the amount instead of choosing only a flat amount or % as it is currently set up in GP version 9. ---------------- 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/Commun...

Results of formula vs real numbers
HI. How do you change a formula to read the value as a result of a formula and not necessarily as a real number? Example: In G6, i have the following formula that gives me the hour of a time in F6 =IF(F6>0,MOD(F6-"1:00",1),"") F6 contains 00:30:08, The result is 23 In another cell, I am using this formula, but it doesnt recognize the 23. =SUMPRODUCT(--(B6:B58331="james"),--(G6:G58331="23")) The 23 is actually the HOUR of time so it isn't a whole number or real number persay. It is a rounded time to the hour. Yes, but it isn't a text ...

protect formulas without password
is there a way i can protect the formulas without protecting and passwording and identifying various ranges? the user accidentally enters data into a calculated cell and wipes out the formula. thank you, mike g Mike, Set the cells that the user can change to unlocked using Format, Cells, Protection, set those they can't to locked. Protect the Sheet. Robin Hammond www.enhanceddatasystems.com "work" <mike@radiant.net> wrote in message news:42140299.10B3@radiant.net... > is there a way i can protect the formulas without protecting and > passwording and identifying ...

Ignoring #DIV/0! in a formula
Is it possible to ignore a cell in a formula if the data is #DIV/0 ive tried messing around with IF statements but I cant seem to figure it out =IF(ISERROR(cellref),erroraction,noerroraction) =IF(ISNUMBER(cellref),noerroraction,erroraction) You can also use it in range functions, such as =SUM(IF(ISNUMBER(range),range)) but you must array enter (Ctrl-Shift-Enter) Jerry DLZ217 wrote: > Is it possible to ignore a cell in a formula if the data is #DIV/0 ive tried > messing around with IF statements but I cant seem to figure it out "Jerry W. Lewis" <post_a_reply@no_e-ma...

If, say, =SUM(I2:I10) totals "0", how to change formula to put a "0" in I11?
I know this is going to be simple but everything I've not got anything to work. How can we change formula, pls, to have "=SUM(I2:I10)" show up as "0" in I11 if there aren't any values anywhere in I2 to I10? Thanks. :oD If there are no values to sum the formula should already be returning 0. What result are you getting? Biff "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message news:OV$7dJLnHHA.3704@TK2MSFTNGP02.phx.gbl... >I know this is going to be simple but everything I've not got anything to >work. How can we change f...

coping formula
I am trying to copy a formula down a column. When I copy the formula down I want the cell to be blank until i fill out the cells to give me my balance. My three columns are Debit / credit / balance. My formual reads : f7(balance column)+e8(credit column)-d8 (debit column) when i copy the formula down form my balance column it gives me the balance in every cell. I would like for it to be blank until i fill in my debits and credits. I hope you understand the way i wrote this thanks much -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200803/1 You ...

MATCH formula #2
I have a spreadsheet that lists all the types of machines and thei model numbers. When a customer places an order for a specific machine I need to reference the other workbook in order to get the model number Is there a way to have this done automatically. For example: when enter a machine type (CE-10), I want the model number for that machin to be entered automatically in another cell, without having to open th other workbook and copy and paste the number myself. I considered dat validation (using a list) but can't reference another workbook. Any thoughts? Thanks, De -- da ------...

Formula Cell not updating
I have a workbook with several worksheets in it. The sheets are not related to each other except that I used the copy worksheet feature to add sheets. There is a formula cell which totals the two cells to the immediate left on each worksheet. For some reason, when I change one of the values in the cells being added, it doesn't update the total, unless I retype the formula in the cell. I have had this problem one other time in the past. I'm not sure how to fix it. -- Sincerely, Beverly76 Hi you may check if automatic calculation is enabled ('Tools - Options - Calculate...

Excel: Replace strings with a Matrix formula
Hello NG, the following sheet: - In column A are words in German. - In column B are the translated words in English. - In C1 is one English clause with only one single German word in it. ToDo: I want translate this word in English and write it o D1. But I dont want to use VBA or more then one cell for the calculation. Such solutions I can make for myself. I'm working since several years with Excel and VBA. Currently I'm playing with this matrix formula. (I have translated the Excel function names from German to English, but I dont know if I used the right words.) {=IF(ISERROR(SEARCH...

This form Requires Word
Thanks gang, I am reasonably up to snuff on Outlook and have used it for as long as it has been around. I am running Windows 2k Pro and Office 2k Pro and Outlook is configured to use Word as the default email editor. It does so quiet well both composing and reading email, except when email is receives from one particular person, one out of hundreds. This lady <the sender> is also using Outlook 2k pro and windows 2k pro and word as default editor. When I receive mail from her and try to open it I receive the message "This form requires Word as your email editor, but Word is eith...

>< formulas
A spreadsheet containing two colums. Column A has dollar amount entries. Column B has date entries. These entries are entered throughout the month and every month of the year. I need a formula that will sum the amounts in column a only if they are during the month of march from column b (for example) "mpiton" <mpiton@discussions.microsoft.com> wrote in message news:F1965B8B-EF82-41A6-89CF-86FF72706DB2@microsoft.com... > A spreadsheet containing two colums. Column A has dollar amount entries. > Column B has date entries. These entries are entered throughout th...

Excel formula help #5
I've come up against a problem in Excel, I need help to figure out a formula. A B C D 1 Number Of Months 24 �60.00 �1,440.00 2 Number Of Weeks 96 �15.00 �1,440.00 I will try to make this clear to understand, what I need is when I change cell C2 either adding more money or taking away, I need cell B2 to reflect the change either increasing the amount of weeks needed to get to D2s total or decrease depending on what was changed in C2. Any advice or help would be much appreciated. On Oct 10,...

Lookup Formula
I have an Excel sheet that shows hourly rates based on the number of hours a customer purchases. Example Column A Column B Column C (Hours)From: (Hours)To: Hourly Rate 1 40 $35 41 80 $32 81 120 $30 I have a seperate column under a different tab where a sales rep enters the total number of hours. I would like the formula to automatically lookup the chart above and show the appopriate hourly rate. Thanks, Donne Hi - best to ask the Excel gurus in an Excel n...

Protecting Excel Formulas
Can anyone help with the protection of part of an Excel worksheet (Excel 2003)whilst still allowing sorting and filtering of data? Individual users paste data into the worksheet and, despite comprehensive instructions, sometimes manage to delete / overwrite existing formulas. The individual users need to sort and filter the whole worksheet after their "input" so the basic procedure of locking and protecting the cells containing the formulas isn't an option. Any help would be most welcome. If users input data to w/sheets they do not need access to cells containing ...

Formula to Calculate Dates
I need to enter one date, and have it compute the future date based on the number of years: For Example: - Date Added: June 2007 - Number Of Years: 5 Year - Expiration Date (Need Formula): June 2012 How would I get that result. BTW: Using Excel 2000 =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) -- Kind regards, Niek Otten Microsoft MVP - Excel "S" <S@discussions.microsoft.com> wrote in message news:8504259F-4193-4CE3-9387-EAB68624076E@microsoft.com... |I need to enter one date, and have it compute the future date based on the | number of years: | | For Example: | - Date Added: J...

VLOOKUP formula searching multiple worksheets ??
Does anyone know how to search for info from multiple Excel worksheets using the VLOOOKUP formula or any other formula. I have my VLOOKUP formula(column B) and list of values(column A) (sorted in ascending order) on Worksheet 7 and I need to search for these values in Worksheets 1,2,3,4,5& 6 Column F. Is this possible with Excel ? I've tried this by using the formula VLOOKUP(A2,Wrk1:Wrk6!$F:$G,1,FALSE) but I get a #VALUE! error. You will need a construct along these lines: =IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)), IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)), IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2...