SumIf Question? If question? I'm stumped..

I have three columns.  "A", "B", and "C".  I want to sum column "C" when "A" 
equals a specific value AND "B" equals a specific value.

For example, sum the amuunts in column "C" when "A" equals "ball" AND "B" 
equals "bat".

Any ideas?

-Adam
0
adam1685 (340)
10/20/2006 9:01:02 PM
excel 39879 articles. 2 followers. Follow

3 Replies
466 Views

Similar Articles

[PageSpeed] 36

You can use an array formula, you need to press CTRL+Shift+Enter after typing 
the formula, if you've done this correctly curly brackets {} will appear 
around the formula in the formula bar:

Use a formula something like:

=SUM(IF(($A$2:$A$6="ball")*($B$2:$B$6="bat"),$C$2:$C$6))

(it won't work if you don't press CTRL+SHIFT+ENTER to enter the formula)

A Jones

"Adam" wrote:

> I have three columns.  "A", "B", and "C".  I want to sum column "C" when "A" 
> equals a specific value AND "B" equals a specific value.
> 
> For example, sum the amuunts in column "C" when "A" equals "ball" AND "B" 
> equals "bat".
> 
> Any ideas?
> 
> -Adam
0
10/20/2006 9:16:02 PM
You can put this formula in C.

=SUM(IF(A=1, IF(B=2,A+B,"")))

"Adam" <Adam@discussions.microsoft.com> wrote in message 
news:EDB7C40F-AA77-4882-9E5E-C1BE3D90C0B1@microsoft.com...
>I have three columns.  "A", "B", and "C".  I want to sum column "C" when 
>"A"
> equals a specific value AND "B" equals a specific value.
>
> For example, sum the amuunts in column "C" when "A" equals "ball" AND "B"
> equals "bat".
>
> Any ideas?
>
> -Adam
> 



0
jim5337 (13)
10/20/2006 9:40:51 PM
You can actually miss out the IF() form your formula and just have:

=SUM(($A$2:$A$6="ball")*($B$2:$B$6="bat")*$C$2:$C$6)

still array entered or make it a SUMPRODUCT() formula:

=SUMPRODUCT(($A$2:$A$6="ball")*($B$2:$B$6="bat")*$C$2:$C$6)

-- 
Regards

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"Andrea Jones" <AndreaJones@discussions.microsoft.com> wrote in message 
news:1C513216-C548-495B-BBFA-99A20FB079FF@microsoft.com...
> You can use an array formula, you need to press CTRL+Shift+Enter after 
> typing
> the formula, if you've done this correctly curly brackets {} will appear
> around the formula in the formula bar:
>
> Use a formula something like:
>
> =SUM(IF(($A$2:$A$6="ball")*($B$2:$B$6="bat"),$C$2:$C$6))
>
> (it won't work if you don't press CTRL+SHIFT+ENTER to enter the formula)
>
> A Jones
>
> "Adam" wrote:
>
>> I have three columns.  "A", "B", and "C".  I want to sum column "C" when 
>> "A"
>> equals a specific value AND "B" equals a specific value.
>>
>> For example, sum the amuunts in column "C" when "A" equals "ball" AND "B"
>> equals "bat".
>>
>> Any ideas?
>>
>> -Adam 


0
sandymann2 (1054)
10/20/2006 10:00:32 PM
Reply:

Similar Artilces:

sumif formula #2
At the moment I am trying to work a sumif formula The problem I am encountering is once I have set the range and criteri it won't pick up the sum range as the range I am specifying is fo example J15:CB45 - it will pick up J15:CB15 but I need it to pick u the whole range - do you know of anyway I can resolve this. My formula is =sumif($J$3:$CB$3,E$3,($J15:$CB45)) but it doesn't see to work this one does though =sumif($J$3:$CB$3,E$3,($J15:$CB15)) bu that doesn't help me! I have put a print screen of the spreadsheet I am trying to work on. Thanks Jenni -- Message posted from htt...

Graph/Chart Question
I do not use the chart/grph feature too often. I was trying to use the Chart Wizard to graph the following data: A B C 3-Jan 6.16% 482202 7822780 4-Jan 5.87% 398392 6788552 5-Jan 2.60% 365581 14064487 8-Jan 5.89% 359353 6102299 9-Jan 3.80% 372896 9812270 10-Jan 4.42% 402357 9102141 I think I need to have 2 different scales (% and Natural). I could not figure out how to graph this data. Thank you in advance. On Thu, 12 Jul 2007, in microsoft.public.excel.charting, carl <carl@discussions.microsoft.com> said: >I do not use the chart/grph feature too often. I was trying to use th...

preparing for an exam and have a question
Hi! I'm prepering for an 70-536 exam and in this book is the question below. I asked this question before but didn't include all the alternatives and the book motivation so this is the complete question with the books motivation. You need to create a simple class or structure that contains only value types. You must create the class or structure so that it runs as efficiently as possible. You must be able to pass the class or structure to a procedure without concern that the procedure will modify it. Which of the following should you choose ? A. A reference class ...

HA design question 07
We are looking to use CCR on our Mailbox servers, but we only have one CAS server in the currently design. If we loose the HAS server can the Outlook 2007 client still connect to the mailbox? Do the Outlook 2007 clients connect directly to the Mailbox server or through the CAS server? My understanding is that Outlook will still function if you lose your only CAS. OWA, Activesync, and Autoconnect will fail though. Question: If you're building a HA environement you want at least two HT servers, right? Install your CAS role on your two HT servers and voila. "Ryan Sanders&qu...

Update question
Soory if this is the wrong place to ask this, but cannot find a NG proper to Windows 7. I currently run Windows Vista and am getting tired of Vista's decision to tell me that it is going to shut down in less than a minute. So have decided to upgrade to Windows &. I note that I can purchase an upgrade versiom for 64 quid from Amazon or an apparently full version of Windows 7 Home premium for 89 quid. My question is this - If I buy the upgrade version will I only be able to load it on a new PC in the future if I already have windows Vista installed? i.e For any future cl...

Date question
My macro is embedded in an Excel workbook. On 'Sheet1' there are 2 input fields, (1) the 'Start' date, and (2) the 'End' date (for a 1 week period). Users will enter both dates. There are several restrictions: (1) the 'Start' date must be a Sunday, (2) the 'End' date must be a Saturday. and (3) these dates can only be for a 1 week period (7 days). There are edits already in place to identify issues for these 3 items. It will also be okay for users to enter a week in the future. However, they cannot enter last week's dates. ...

SUMIF or SUMIFS
Here is what I have Col A Col B Col C 1310 3 3,463.00 1315 3 740 1330 3 1369 3 -178 1375 3 -105 1640 3 110 135 4 1310 4 1,460.00 1315 4 1,521.00 1375 4 -65 1310 6 3,284.96 I am trying to figure out a way to add column C to a new cell if Column A is between 1310 and 1369. Any suggestions?? =if(and(A1>1310;A1<1369);C1;"") HTH "Jeff" wrote: > Here is what I have > > > Col A Col B Col C > 1310 3 3,463.00 > 1315 3 740 > 1330 3 > 1369 3 -178 > 1375 3 -105 > 1640 3 110 > 135 4 > 1310 4 1,460.00 >...

Several questions about List control
Hi Friends, I got several questions, please help. 1) How to prevent the cursor from changing into a "hand" when it approaches the checkbox in the list control? 2) I have troubled making the checked checkbox. "LVIS_SELECTED" doesn't seem to work m_list.SetExtendedStyle (LVS_EX_CHECKBOXES |LVS_EX_ONECLICKACTIVATE| LVS_EX_FULLROWSELECT ); for (int i=0; i< 7; i++) { int nPos = m_list.InsertItem(i, "test"); m_list.SetItemData(nPos, LVS_ALIGNLEFT );//|LVS_NOLABELWRAP ListView_SetItemState (m_list.m_hWnd, nPos, LVIS_SELECTED, NULL); } 3) How to prevent the...

Counting question
In column A I have set of characters that are entered into the rows below (i.e. Y, N, MRO) in column B I have another set of characters that are entered into the rows below (i.e. A, E, on so on). I want to count the instances when the rows in Column A contain a Y and the rows in column B contain an A. Is there an simple formula for doing this? Any help will be appreciated. -- John =COUNTIFS(A:A,"Y",B:B,"A") Regards Steve "John" <John@discussions.microsoft.com> wrote in message news:62A26E1C-E421-41E6-834C-B8547707E2D4@microsoft.c...

HELP: directory question
Thisw may be off topic. Sorry if it is. I've got an application, and it gets installed to a particular directory (they choose) on a users computer. How do I find out what this directory is at runtime? I want them to install a license file, and then my application check the values in the license file at runtime. Problem is, the directory could be different for each user. Hamish Dean >-----Original Message----- >Thisw may be off topic. Sorry if it is. > >I've got an application, and it gets installed to a particular directory >(they choose) on a users computer. How ...

Technical Question
Hi all I have a main server with some agents which each agent acts like a server. in each agent SqlServer is installed. How can I integrate all agents's data into main server. I know a solution is to merge them. Is there a better way to merge them ?? thanks in advance pers What is the vesrion of SQL Server are you using? "pers" <rezarms@hotmail.com> wrote in message news:ugfM8bhrKHA.3408@TK2MSFTNGP06.phx.gbl... > Hi all > I have a main server with some agents which each agent acts like a > server. > in each agent SqlServer is installed. ...

VPN question....
HI, its possible to access my RMS databse wich is located in other city from my local office RMS Manager via VPN? is that doable? thanks! I see, since it will be the VPN network i can just type the regular server name, and database name in the RMS ADMON. Im in the right way? Thanks@ So is that perfectly possible to accomplish? I think i must use the 1433 port right? Thanks "Rob" wrote: > Yes, once you setup your VPN you need to set the SERVER Name as the LAN IP > address of the SERVER in SO ADMIN. > > Rob > > "Aldo" wrote: > > > HI,...

sumif across multiple sheets in excel 2007 based on a condition
I have a small work book, tracks deliveries made and trucks used. I have seven sheets Saturday thru Friday and one sheet that totals everything up! The weekly sheets have 34 rows representing 34 stores that we service and the total sheet also has 34 rows that contain the totals for that week. Here is my question I am trying to, on the totals sheet, sum up the total trucks that we used per store per week. In the cell would be entered 0, 0.5, 1, 2 and so on depending on how many trucks we used for that day. Yes we use 0.5 if the store was combo'ed with another store. I ne...

Detect & Repair Question Please
I am running XPPro & Office 2000 Small Business edition. Every so often Outlook refuses to start & I must run "detect & repair" and that fixes it okay (I still don't know what's causing this however). My question is: when I chose "detect & repair" I an asked to put in my original installation disc. This is a pain. Surely whatever detect & repair is looking for can be installed on my hard drive so I don't have to keep going back to the install disc. Help please. Thanks in advance... Bob ...

another sumifs plea
I'm struggling to convert a sumifs line from 2007 to excel 2003. The line I have working correctly in 2007 is: =SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">28/2/2010")-SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">31/3/2010") Can any one please help me to put this into 2003? Best Regards Dave =SUMPRODUCT('Washing MC Rental'!$K$4:$K$21,...

Question on Printer Setup
I have two printers, one POS receipt printer and one laser printer. What i would like to do is is just use the laser printer because it has the 8 1/2 X 11 paper and i can put more info like company policy, items and with descriptions etc. My problem is i need a way to open the drawer and the only way would be is print out with the pos receipt printer. Is there a way to print out with both at the same time so the receipt printer will open the drawer and the laser would be for the invoice? Or does anybody have a solution for this? This is a multi-part message in MIME format. ------=_N...

SUMIF or SUMIFS help
I have 2 sheets in one workbook (Sheet 1 and Sheet 2) Sheet 2 has 3 columns: A B C MAKE TYPE QTY 1 toyota compact 10 2 ford pickup 15 3 toyota sedan 20 4 toyota pickup 80 5 nissan hybrid 10 Sheet 1 has 2 columns: A B MAKE PICKUPS 1 toyota ? (SUM) I need Sheet 1,B1 to calculate the total number of matching items in sheet 2 that matches the data entered in Sheet 1,A1. In other words, I need sheet 1,B1 to automatically sum up the total number of to...

OU Question!!!
We have installed CRM 1.2 on dev server using existing database(backup of prod db). While installation we pointed Organisation Unit to "CRMDev" (created manually under Active directory). but once installation is completed i dont see any Organisation name and usergroups under "CRMDev" Organisation Unit. Any reason why it didn't create? Is it using the same OU as prod. Is there any setting that needs to be changed in DB and point it to new OU. Please Suggest... Where we did it wrong: As posted in another post suggestion to -create a OU in AD called devcrm -backup...

Hardware Question
Hi, I have been running a Microsoft Exchange cluster for close to 5 years now. It is running on a Storagetek SAN attached (FC) to a Storagetek Library (FC). Well its time to upgrade my systems due that I cannot renew my warranty them past next year again. So I been starting to shop around for new hardware and I was wondering what you guys think about iSCSI SAN Storage solution to cluster my 3 Storage Groups that add up to 120gigs. We have 500 employees. So my plan is to get a iSCSI Tape Library and iSCSI SAN. What do you guys think and what models should I look into? George Spiro ...

sumif vs sumproduct question
Hi, I have a simple spreadsheet with column B containing all dates, and column D containing all numerical values. The dates range throughout a year. At the bottom of the sheet I've entered January through December in another column. To the immediate right of the month named, I have a formula which gives me the total for that month. For instance, next to January my formula reads: =sumproduct(--(month(b6:b370)=1),d6:d370) this formula works, but it seems to me I should be able to use SUMIF too. I've tried =sumif(d6:d370,month(b6:b370)=1,d6:d370) but that just returns a blank ce...

SUMIF function?
I'm tring to get excel to do a conditional sum based on the following. I have two columns, the first containing a reference number, and th second an ammount which is either positive or negative. In another sheet I have the reference number and beside it i want cell to calculte the sum of all the sells with that ref number and tha contain a positive amount, see the example below. Ref # Ammount 1 -5.15 1 6.00 1 -3,50 2 2.20 2 -3.40 2 2.40 3 6.40 3 -7.20 3 -1.80 SO then on another sheet i have Ref Positive Total Negativ...

spam question #3
What does it mean when a reply to an email has this: [!! SPAM] in the subject line of the original email that had been sent? pdwas, you wrote on Fri, 17 Apr 2009 22:28:01 -0700: > What does it mean when a reply to an email has this: [!! SPAM] in the > subject line of the original email that had been sent? This hint has been added by a spam tool that has identified this mail as SPAM. Outlook's own Junk-Mail-Filter does not modify the subject or the mail body of an spam email. You should be able to disable adding this hint in the options of the tool you're using for malwar...

SUMIF and a criteria range
Hi, I would like to use the SUMIF statement for summarizing when the criteria is a range and not a fixed value. Example: I have entered several invoices in column A, Arrival date in column B. On a different sheet I would like to summarize the invoices that have invoicedate between the first of and the last date of the month. Can this be done with SUMIF or is there a another way to fix this? Best regards Fredde Pretty simple. Did you try before asking? =SUMIF(A:A,G15,C:C) -- Don Guillett SalesAid Software donaldb@281.com "Fredde" <oleander@rocketmail.com> wrote in messa...

money /pda questions
1) How do I set up my money pda version to not display cheques that have been cleared. I am able to choose this option on my laptop but unable to do this on my pda. I am currently using activesync to syncronize. 2 Am I able to use bills and payments on my pda and not only on my laptop. If so how. issue 2 - unfortunately, not. I will be more than happy if it can. I also hope the PDA can alert me of upcoming bills... Other more critical design flaw with the PPC version: i) Even if have a password for your Money on the laptop. I can sync your Money data to my PDA without your password: i...

Denominations Question
Hi, I have a sheet with amounts to be paid to employees. The employees need to be paid in cash every week. These amounts normally don't exceed $1000. However, it is a nightmare if the exact denominations are not withdrawn from the bank. Is there some way I can determine the number of currency notes to be withdrawn in denominations of 1000, 500, 200, 100, 50, 20, 10, 5 & 1's? Can I do this in excel? Thanks and regards, neil Neil, Assuming the amounts to be paid are in column D starting D2 down: Populate cells E1 through M1 with the denomitations 1000, 500, ...., 1 Put th...