Sumif with two criteria

I want to do a sumif() command if cell a=x AND b=y.
0
danperez (3)
8/26/2003 7:49:27 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
662 Views

Similar Articles

[PageSpeed] 49

Dan,

=SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10))

Change the range references and criteria as needed.

PC



"Dan Perez" <danperez@joannstores.com> wrote in message
news:040101c36c0b$2874d010$a301280a@phx.gbl...
> I want to do a sumif() command if cell a=x AND b=y.


0
pcorrado (50)
8/26/2003 7:58:08 PM
Paul Corrado wrote
> =SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10))

> Change the range references and criteria as needed.

Very good solution. One little problem I have found with this in the past.
Suppose B1:B10 contains text like as illustrated below.

boat club
banana boat
fishing boat
sailing boat

What we want to do is sum the cells containing "boat" using the same
formula. B1:B10="boat" or even B1:B10="*boat*" did not work for me.

Mark



0
8/26/2003 8:31:40 PM
yes, the function description mentions this explicitly.  
Wildcards cannot be used here.

>-----Original Message-----
>Paul Corrado wrote
>> =SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10))
>
>> Change the range references and criteria as needed.
>
>Very good solution. One little problem I have found with 
this in the past.
>Suppose B1:B10 contains text like as illustrated below.
>
>boat club
>banana boat
>fishing boat
>sailing boat
>
>What we want to do is sum the cells containing "boat" 
using the same
>formula. B1:B10="boat" or even B1:B10="*boat*" did not 
work for me.
>
>Mark
>
>
>
>.
>
0
kiii63 (59)
8/26/2003 8:47:18 PM
But they can here:

=SUMPRODUCT((A$1:A$5="x")*(SEARCH("*boat",B1:B5)*(C1:C5)))

Alan Beban

jr wrote:
> yes, the function description mentions this explicitly.  
> Wildcards cannot be used here.
> 
> 
>>-----Original Message-----
>>Paul Corrado wrote
>>
>>>=SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10))
>>
>>>Change the range references and criteria as needed.
>>
>>Very good solution. One little problem I have found with 
> 
> this in the past.
> 
>>Suppose B1:B10 contains text like as illustrated below.
>>
>>boat club
>>banana boat
>>fishing boat
>>sailing boat
>>
>>What we want to do is sum the cells containing "boat" 
> 
> using the same
> 
>>formula. B1:B10="boat" or even B1:B10="*boat*" did not 
> 
> work for me.
> 
>>Mark
>>
>>
>>
>>.
>>
> 

0
beban (93)
8/26/2003 9:15:10 PM
What's in I9 and K9?

Alan Beban

Chris wrote:
> Try this, must be entered as an array...
> 
> {=SUM(IF((I13:I21=I9)*(K13:K21=K9),M13:M21,0))}
> 
> adjust range...
> 
> 
> 
>>-----Original Message-----
>>yes, the function description mentions this explicitly.  
>>Wildcards cannot be used here.
>>
>>
>>>-----Original Message-----
>>>Paul Corrado wrote
>>>
>>>>=SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10))
>>>
>>>>Change the range references and criteria as needed.
>>>
>>>Very good solution. One little problem I have found with 
>>
>>this in the past.
>>
>>>Suppose B1:B10 contains text like as illustrated below.
>>>
>>>boat club
>>>banana boat
>>>fishing boat
>>>sailing boat
>>>
>>>What we want to do is sum the cells containing "boat" 
>>
>>using the same
>>
>>>formula. B1:B10="boat" or even B1:B10="*boat*" did not 
>>
>>work for me.
>>
>>>Mark
>>>
>>>
>>>
>>>.
>>>
>>
>>.
>>
> 

0
beban (93)
8/27/2003 1:48:17 AM
jr wrote
> yes, the function description mentions this explicitly.
> Wildcards cannot be used here.

I was unaware of this but I do believe it must be true. Thank you.

Mark


0
8/27/2003 4:34:27 PM
I posted the following in this thread yesterday:

=SUMPRODUCT((A$1:A$5="x")*(SEARCH("*boat",B1:B5)*(C1:C5)))

Alan Beban

Mark Tymes wrote:
> Chris wrote
> 
>>Try this, must be entered as an array...
> 
> 
>>{=SUM(IF((I13:I21=I9)*(K13:K21=K9),M13:M21,0))}
> 
> 
>>adjust range...
> 
> 
> Thank you, Chris. I do not have excel on this computer but I tried your
> suggestion on my daughter's who lives nearby. It worked but only up to a
> point. In cell K9 I entered "boat" but this only returns the sum of cells in
> K13:K21 where the word boat is unique in that range. For instance it would
> only pick out the bottom feature in the illustration below.
> 
> boat club
> banana boat
> fishing boat
> sailing boat
> boat
> 
> 

0
beban (93)
8/27/2003 5:03:22 PM
Reply:

Similar Artilces:

How to best compare two timelines having the same sequence?
I am trying to graphically compare the timelines of two governmental legal actions having identical procedural steps (administrative through judicial), but with different factual circumstances at each procedural step. Otherwise stated: At each stage of the above process, I am trying to illustrate and compare the different factual circumstances in the two legal actions. ...

Vlookup based on multiple criteria
I have 2 workbooks: Workbook one: Account number, name, January to September + total (columns A9 to L240) Workbook two: I need to populate with data from Workbook one Column A has the account numbers all accounts that contains department 71(xxxxx-71-xx) must go to location 1 (Jan - column B, Feb =96 column E etc) and all others goes to location 4 (Jan =96 column C, Feb =96 column F etc) Is there a basic a VLOOKUP or other function that can automatic the tasks? Thanks in advance. Workbook one Account Number Jan-10 Feb-10 Mar-10 41001-71-01 6,000.00 - 1,000.00 41001-71...

two copies of every email in crm3
Hi Whenever a user sends an email message from within a crm3 account as an activity, two instances of the original message subsequently appear in "history." how do i ensure that only 1 instance of an email is kept? many thanks in advance This does happens if you sent an email from one Crm user to another. If this is the case you can avoid this by going to Settings > Organization Settings > System Settings and on the tab E-mail Tracking set the second radio button (Exclude e-mail) to Yes. If it is not user to user mail the above won't change that. -- Patrick Verbe...

counting using multiple criteria
Hi, I have a problem for which I can't seem to find a working solution. On the one hand I have a vertical table with employee names, the number of years they have been with the company, and all this sorted by the employee's age (in years). This table will be updated from time to time, and as such it doesn't have a fixed length. On the other hand I have a table which shows the number of employees in a particular age category (-20, 21-25, 26-30, etc) horizontally, and the number of years they have been with the company (again in groups: 0-5 yrs, 5-10 yrs, etc) vertically. ...

Need Help with Sumif Function including dates
Hi, I need some assistance with tracking my commissions that are due to be paid each month. The "Comm Due Date" is in column "C", rows 3 - 30 which list by date all of the Commissions Due to be paid and the "Comm Balance" is in column "H", rows 3 - 30 which has the Commission Balance due for each sale. I tried utilizing a formula from a post that I found from April '05 but for some reason I keep receiving a #NAME? error. I'm not sure what I am doing wrong. I want to find the total due by month so that as I add additional sale...

Combine two cells without losing data cell formats
I need a macro, or a function to combine 2 columns of data into one column WITHOUT losing the text-justification of each. See below. what i have: 1st column is left-justified text. 2nd column is right-justified text. [john ][ smith] [sam ][ williams] [antwane][ carlson] what i need in each cell, which is no larger than a cell width of 88.14 or 622pixels : [john smith] [sam williams] [antwane carlson] if i concatenate, i get the below which is not what i need: [john smith ] [sam williams ] [a...

two types of opportunities, cloning?
Hi all, we would like to use two different types of opportunities in CRM 3.0. One would be for standard products, and one for service. We've been thinking about it and having separate entity for service opps seems just to be the way for us. Easier when adding data, much more convenient to display... So, is there a way of cloning the opportunity entity in a way that we could use its functionality for another entity? Just like deriving a class in OOP. Or any workaround like creating a new entity and linking an opportunity? Or the other way round? Any thoughts on this are welcome. Cheers, ...

Duplicate Detection
I've created a rule for identifying duplicate contacts with the following match code criteria: - emailaddress1 - status This works as expected when I update an existing contact's email address to one that a another contact already has, i.e. only active contacts with that email address are identified as duplicates. However, when I create a new contact and give it the same email address as an existing contact's (active or inactive) then no duplicates are identified. This is not what I expect. Has anyone come across this behaviour before? How can create a rule that fulfils my...

macro for comparing fields in two work books
Hello, I have two excel spread sheets (say A1 & A2). A1- is the master sprea sheet and A2 is smaller spread sheet with very few details. Suppose Column B in A2 has 100 partnumbers and Column C in A1 has th superset of partumbers (1000) and corresponding details for each par number, I need to: 1. Check if all the 100 part numbers in A2 has a corresponding match i A1 2. Extract the info for the matched partnumbers from A1 and list in seprate sheet. 3. Even if there in no match A1, let's say for 40 of them, it shoul still list those parts in the new sheet. Can anybody help me with t...

If Statement and Multiple Criteria
I am using a formula to bring back some text about some data. I am using the following formula: =IF(N12>M12,"is larger for girls","is larger for boys"), so that if the data in cell N12 is greater than the data in cell M12 then it says "is larger for girls" and vice versa. This works fine, apart from if negative numbers are in the cells then it brings back the value closest to zero - rather than the largest negative number. Does anyone know how to get round this. In addition - I would ideally like to alter the formula so that it says that if the two...

How to subtract two fields
Hi, I have a form with two fields Current and pervious I made a textbox to subtract them and view the result but it gives me an error Can any body help me ghost, = [Field1] - [Field2] A clear question gets a clear answer... 1. What "types" of fields are these? 2. What calculation did you use, and where did you place that calculation? 3. What error did you get? 4. What would be some sample values, and expected results? -- hth Al Campagna . Candia Computer Consulting . Candia, NH USA Microsoft Access MVP http://home.comcast.net/~cccsolutions ...

Accessing two tables from a single mfc application
Hi, I need to know how to access two tables using a single mfc(vc++) application. I also need to know how to extract data from a table using an mfc application using a primary key in a table.It is very important that I get answers for these two questions as this is part of my final year college project and I have very little time to complete this. Please Help! Thanks, Satish. "Satish Chandrasekar" <yourdisplayname@discussions.microsoft.com> wrote in message news:A0D7EC0A-18C3-4375-B5A5-AFDB1AB2F577@microsoft.com... > Hi, > I need to know how to access two tab...

Sumif with two criteria #3
I am trying to do a sum if with two worksheets as follows: Worksheet 1 (A1) Area Code B C (A2) 111111 This Year 50000 (A3) Last Year 25000 (A4) Budget 75000 Worksheet 2 (A1) Area Code This Year (A2) 111111 Where the formula is I did the following =SUMPRODUCT(((Worksheet1A1:Z100=A2)*(Worksheet1A1:Z100="This Year"), Worksheet1C1:C100) I am getting the message #VALUE!. In the first part of the formula I am looking am trying to match the criteria to wo...

Formula help
I am looking for a formula or function that will sum columns based on meeting specific criteria. for example, I have an age table that with a minimum age column then a maximum age column. I need to be able to enter an age in to a seperate cell and have Excel sum based on the ages. Min Max Non Tob Spouse Child EE ADD Sp ADD Ch ADD 15 24 0.46 0.69 0.535 0.679 0.266 0.14 0.072 25 29 0.53 0.78 0.45 0.679 30 34 0.65 0.97 0.49 0.679 35 39 0.9 1.46 0.675 0.679 40 44 1.24 2.19 0.97 0.679 45 49 1.99 3.48 1.52 0.679 50 54 3.05 5.87 2.365 0.6...

Conditional formatting formula with multiple criteria
Hi all, Have been puzzling over this for a while now, and can't get it right! Using conditional formatting, I want to: If any of the cells in Column B are blank, and the cells in Column C are blank, and the date in Column D is less than todays date - 3 days, turn the cell bold and red Can someone please help? I have tried all different variations and just can't get it! Thanks, Kirstie Kirstie Your questions sound like you wish to pick up whether there is a blank ANYWHERE in column B AND (not or) ALL cells in column C are blank AND that the date in a single cell in colum...

Diff between two dates formatted as years and months
I am trying to display an employee's length of service at a particular date using a simple formula to subtract one date from the other and format the result as y"y" m"m" so that I get eg. 2y 4m as the person's service. It is returning odd results eg. 0y 12m for diff between 1/1/04 and 1/1/05 and 1y 1m for diff between 31/12/03 and 1/1/05. Is there a more accurate method of doing this to ensure that I get the result I want? Thanks for all your help A complete explanation: http://www.cpearson.com/excel/datedif.htm -- Kind Regards, Niek Otten Microsoft ...

How to have two separate overhead rates per cost type?
Currently, we are on GP 9 and utilize Wennsoft job cost. Somewhere prior to my arrival here, our company was told you can only have one overhead rate per cost type (i.e. one overhead % to labor). So I've been charged with trying to figure out a way around that. Currently, we have gross margin set for our overhead cost factor and it is used in the payroll entry zoom screen for the Overhead Cost calculation. That has to stay the same. However, the company would like *displayed* on the same screen the % that we would be allocating as operating margin overhead, but not used in thos...

vbscript insert into access 2003 database with two different table
I am trying to insert data collected by WMI. Here is the script On Error Resume Next Const HKEY_CURRENT_USER = &H80000001 Const HKEY_LOCAL_MACHINE = &H80000002 Const ForReading = 1 'Create FSO Set objFSO = CreateObject("Scripting.FileSystemObject") 'Create an environment for the script to work Set wshshell = WScript.CreateObject("WScript.Shell") 'Connection to the database Set cnn = CreateObject("ADODB.Connection") 'Connection to a Recordset Set objRecordSet = CreateObject("ADODB.Recordset") 'Opens the Database ...

Two lines and pne column
I am trying to modify one of the custom charts to show two line and one column rather than two columns and one line. can anyone help. Ian - Forget the custom type. Make a chart with all three series as lines, select the one series, choose Chart Type from the Chart menu, and select the Column style you want. Voila, your first custom combination chart. There's more about Combination Charts on my web site: http://peltiertech.com/Excel/Charts/ComboCharts.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech....

Two formulas in one cell
Correction: This did not work for what I need. I need to show the totals found on the second row, i.e. the totals of the 1.0, 2.0, etc. but keeping separate and together. I have an excel sheet showing what I need if I am too confusing here. Can you give us a simple example best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme of the data and the expected results? "something68" <something68@discussions.microsoft.com> wrote in message news:35ECB9C7-9B22-4CBA-9547-25801E3608DA@microsoft.com... > Correction: This did not work ...

one form to populate two tables
Hi Everyone, I am new to Access and am learning a lot but I've a question. We get equipment in here to evaluate and repair all the time. In my DB I want to be able to enter data on my RA form, have it update the RA table (which it does) but also populate certain feilds on a second table called Eval table. This is so when the Eval report is run some of the feilds are filled out already. We always have to fill out an RA but we fill out the Eval form only whenevaluating equipment for repair. Hope this makes sense. Any help will be greatly appreciated! -- John.. John If...

Create a report based on information from two forms
Hi I need to create several reports which have the appearance of the forms in my database. The top half of the form/report contains information that must be on every report so I created a base report. In the top half, I have formulas which are correct. This report/form is called Students. The bottom half of the form is in reality a subform. What I've done is saved the forms as reports (using the same file name except that these names start with R for report), Then I saved the R-Student report with a new name so I can combine the information for the second half. (...

Advanced Find should let me search records between two dates
When searching the system for records, many times I need to search for records that came in between two dates. For instance, I would like to be able to pull all records input in CRM between March 1 and March 15. In 3.0 you can only query specific dates like "Last X days," "Last X Months," "On," "On or After," etc., but you cannot search the date fields between two dates. The functionality was available in 1.2 but is not available in 3.0. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the...

Capturing from two keyboard on Laptop
SW is running on laptop with another keyboard attached. I'm capturing WM_KEYDOWN messages on PreTranslateMessage to process info. How can I know when I receive the WM_KEYDOWN message if it's coming from the laptop's keyboard or from the external attached keyboard????? I compared the values of pMsg->wParam and lParam and they are the same when the same key is pressed on both keyboards. "Jaime Boloix" <mm197528@hotmail.com> wrote in message news:%234csJuk9DHA.2672@TK2MSFTNGP10.phx.gbl... > SW is running on laptop with another keyboard attached. > I'm c...

Validating two fields
I am trying to create a database in which some of the fields should be mandatory only under certain conditions. For example: In my "Type of Injury" column, I want the user to be required to enter a value in this column, only if the value in the "Type of Document" column is "Nursing Report". Is this possible? I am only just beginning to teach myself Access, so please bear with me. Thank you. Put a validation rule on the *table* (not field.) 2nd example under the the Validation Rules for Tables section here: http://allenbrowne.com/Validati...