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
541 Views

Similar Articles

[PageSpeed] 7

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:

2006 Year End Question
Do I need to close off 2006 and do the year end procedure before opening any periods in 2007? We are a little behind in data entry for 2006 and will run into January to finish off 2006. In the meantime we want to be able to keep current with 2007 if at all possible. What would be the impact of opening up 2007 periods before doing the 2006 year end and will the system allow it? We are using GPv8 with SQL Server 2000. Thanks, Nawaz You can open up 2007 periods right away. We've opened up 2007 already for our January rent. (We closed it again to prevent year end entry errors.) In some ...

SUMIF Ranges
Hi Is it possible to use a range of values within the SUMIF formula? I need to be able to SUM the values against a range of criteria e.g range of AB0001320-AB0001330. Is it possible to use multiple SUMIF's within the same formula? Thanks Au -- aus7 ----------------------------------------------------------------------- aus74's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=281 View this thread: http://www.excelforum.com/showthread.php?threadid=32012 Hi check out SUMPRODUCT - i think you'll find it a better choice for multiple criterias than SUMIF....

"out of office" question
I am running exchange 2003 and outlook 2003. How can I make it where an admin can set "out of office" for any mailbox? Mostro wrote: > I am running exchange 2003 and outlook 2003. How can I make it where > an admin can set "out of office" for any mailbox? Not natively possible unless you log into their mailboxes (via Outlook or OWA) and set it - but see http://www.symprex.com/products/oom/ for one option. thanks for your response "Lanwench [MVP - Exchange]" <lanwench@heybuddy.donotsendme.unsolicitedmail.atyahoo.com> wrote in message news:%2...

Outlook tasks and Exchange question
At work I connect to an Exchange server. Basically we use the tasks feature to keep track of jobs that we're working on. I want to know if it's possible client side to change the font that I see when I read each task. I'd like to see only Verdana size 9 if it's at all possible. Is there anyway to do this client side as opposed to having it set through Exchange? -- Nocturnal @ http://www.randomfix.com ...

Question: How to delete the email history which created by workflo
The whole process is: When a case has been created for 1 hour but not be closed, then the workflow will send an CRM email to the case's owner for notify. After sent an email, there will be a email history in the history, But we don't want to save the email which created by workflow. How can we do that? Any ideas? ...

Summing Question
I have a question: If I have two columns such as: 9.5 2 3.6 3 14 1 22 1 8 2 9 3 7 1 How would I sum all of the numbers in column A that correspond with a 1 in column B? Of course I want to the same for 2, and 3 also. Thanks. On Jan 24, 5:34 pm, Jimmy <nos...@nospam.com> wrote: > If I have two columns such as: > 9.5 2 > 3.6 3 > 14 1 > 22 1 > 8 2 > 9 3 > 7 1 > How would I sum all of the numbers in column A that correspond with a > 1 in column B? =SUMIF(B1:B7, "=1", A1:A7) Assumi...

Cash Flow question (bug?)
Using M04 Deluxe, is there any way, when viewing "multiple accounts" from within the "Forecasting Your Cash Flow" screen, to modify the behavior of crediting and debiting the same forecasted credit card payment to simply debiting? I have two estimated monthly credit card payments in my "bills and deposits" list. When the actual statement arrives, I modify this amount and pay from my checking account. When I use the cash flow analysis tool, and select only my checking account, things are accounted for correctly in that the estimated amounts (or the updated, actu...

Quick Question
I have data in columns A, B, C, D, and E. Sample A B C D E 1 2 3 4 1245 2 5 4 1 4156 1 4 8 0 M1 D5 D-F 1 2154 1 2 3 4 1245 2 5 4 1 1 4 8 0 M1 D5 D-F 1 2154 I want a VBA macro to run searching column E and a message box should pop up at the end of the macro saying something like: "There are 5 (th...

reduce the size of Exchange 2003 database questions please.
Running Exchange 2003 and Windows Server 2003 enterprise My exchange database keeps growing and growing. I’m regularly running maintenance jobs, purging mailboxes and figuring out ways to shrink the database. Can someone explain to me why the database grows when I’m stilling removing mailboxes as needed and doing maintenace? I know I can run eseutil to shrink and defrag the database but I hear a lot of horror stories about running this tool. Another alternative but I want to ask the experts about is what if I decided to create another blank database and move all the mailboxes to ...

Cluster Resource Question
Hey everyone, We are running Windows 2003 ent. and Exchange 2003 ent in a four node cluster (STVS1, STVS2, STVS3) with three nodes active and one passive. A guy from Dell professional services was in here and set up the cluster and left just last week. While Looking the systems over I found an error on nodes STVS2 and STVS3 in Exchange System Manager-->Tools-->Monitoring and Status-->Status. The status reads: "Critical: Service not running". Double clicking each item tells me that the MTA is stopped. Going to Cluster Administrator I can see that there is no MTA r...

Why no body is answering my questions !!!
I posted the following 2 questions below few weeks ago but no body responded!! If the answer to these questions is that it is impossible to set such security on price lists & products then I would like to know that because my manager keeps nagging for the answer :-) The Quetsions: =========== I want to set security constraints so that each sales person is able to see/handle only certain price-lists and not all price-lists. The same also, I want that the each sales person be able to see/handle only certain products and not all products. Can I do the above 2 requirements? If yes, the...

Unmatched Query Question
I have a database that I'm using to pull the names of students who have applied for graduation from an Excel spreadsheet, that is updated weekly, into an Access Table. Right now, I'm using an unmatched query to pull Student IDs who have not previously appeared on the Excel spreadsheet. At first, I was a little worried about this because if a student applies for graduation with a dual degree, their name shows up on the Excel Spreadsheet twice (see example below). 111000 Pig Porky BA.PSY 111000 Pig Porky BFA.FP Fortunately, as long as the student's ID number isn't on the Eval S...

eXtender question
Hello Everybody. We added an eXtender window to the Manufacturing Bill of Material window - we need to include several key fields (PPN, CPN, BoM Type) - When we create a view based on this window, the key fields all get concatenated into one. Is there a way around this? I need each of the individual key fields. Splitting them based on a fixed length per field is not an option. We are using GP10. Thank you for any help you can provide. You need to use EXT00100 table to split out key fields. On Sep 28, 12:39=A0pm, Maria <Ma...@discussions.microsoft.com> wrote:...

outlook 2003 question
Hi All, I am using windows xp home sr3 with outlook 2003. I have several e-mail accounts one I use with outlook express the other I use with outlook 2003. Can I set up outlook with the two accounts and have them go into separate in boxes? Can this be done? Any help would be most appreciated. thanks, -- Regards, Gary Metzler send to: gmtravel@bellsouth.net http://www.outtasighttravel.com Msn messenger: gmetzler1150@hotmail.com Skype name: garymetz pop accounts? or? If pop accounts generally you create folders to sort mail on receipt / send, appropriately ...

On Line bill pay question
I am a recent changover from Quicken and getting very frustrated. I am use to in quicken going to an online section to pay bills where only the online payees(not everyone which has ever been paid) come up and once you save a transaction you do not have to submit each bill for payment each time. In Money if you save draft, you go back two pages and have to go through menus to continue online paying. Any suggestions short of going back to Quicken? Thanks ...

Mail
Hello: I'm just finishing up a 5.5 migration and I know this will go away in 2003 but I need to know this: In 5.5, we got a ton of spam destined for users@ourdomain.com whom actually had no account on our server. How do these spammers pull this off? Looking at the header, I see the mail addressed to noboby@ourdomain.com and yet there are recipients onour server that get it. How does this happen without a valid email address? How does the exchange server know to deliver this mail to john@ourdomain.com when it is addressed to nobosy@ourdomain.com Thanks in advance. May be blind ...

OL 2003 Data File question
Hi. Right now I have one .pst file for my Outlook and everything runs fine. My question is this: Does Outlook 2003 run slower as the .pst data files grow larger. I have many emails I need to keep for business reasons and although it might be ok now, will my Outlook run slow, say, a year or so from now; once the .pst file has grown to say a gigabyte or more, etc.? Any help would be appreciated. Thanks. Andrew ...

FE/BE question
Hi, When an environment has a single FE and BE, during the initial setup, do the permissions change on Exchsrvr folder of either Exch server, or do they simply retain the default permissions (as seen in KB 322935)? thanks, Mel On Thu, 12 Oct 2006 04:54:02 -0700, Mel <Mel@discussions.microsoft.com> wrote: >Hi, > >When an environment has a single FE and BE, during the initial setup, do the >permissions change on Exchsrvr folder of either Exch server, or do they >simply retain the default permissions (as seen in KB 322935)? > >thanks, > >Mel The perms st...

Closing Process Question
From my Sr. Accountant. "Fiscal periods in Great Plains - I closed the March fiscal period for Sale, Purchasing, and Inventory before I left work on Tuesday April 6th. The only module I left open was Financial. On Wednesday April 7th, Cindy was able to post a bank transaction entry to the March period with no problems. Is this the way it is supposed to work? I thought closing the individual modules would prevent someone from posting to the wrong period. Does the Financial module have to be closed to prevent this? Attached is a screen shot of the fiscal periods table as it wa...

RMS
We are going to integrate RMS into Great Plains and i am not sure how Great Plains values stock in the following instances: 1. group level stock values 2. store level stock values 3. inter store transfers (Is the value at group transfer control a/c at store or group ave cost) 4. returns to suppliers from store 5. returns to suppliers from HQ 6. store stock take adjustments (Is the adjustment value - at store or group ave cost) Any clarification on the above would really be helpful. thanks hi Shaun, 1. when you say the group level stock valuation, it is not the case yes it applies once yo...

Radio Button Question #2
I want to add several radio buttons to a dialog but the default style is BS_AUTORADIOBUTTON and the buttons end up being mutually exclusive. How do I make them completely independent? Thanks again! Ron H. ----------------- www.Newsgroup-Binaries.com - *Completion*Retention*Speed* Access your favorite newsgroups from home or on the road ----------------- Radio button was designed to behave that way and that has nothing to do with BS_AUTORADIOBUTTON style. Radio button’s default styles do not include WS_GROUP style and that makes them to belong to a group. Read about "WS_GROUP&quo...

Complex Date chart question
I have a chart of data (2036 rows) that includes a date in each entry/row. Some of the dates occur once, some of them occur several times. I would like to create a chart that shows time (3 1/2 year period) along the X-axis and the numbers of times each date occurs on the Y-axis (between 1 and 20). How do I do this??? Each time I try I just get a jumbled mess. I think a line or scatter graph would work best. One option would be to create a pivot table on your chart source. Your row field would be the dates and your data field would be a count of the dates. Once the pivot table...

autonumbering question
hi all, I have office 2007. Im using access. I have two colums of numbers, the first, column 1 is for a context number, and i enter it by setting a value manually in design view i.e. 140. i want to enter a list of items numbered 1 to 50 in the second column, column 2. when i use auto number it lists the sequence but when i change the value in column 1 for the next context i.e. 142... the numbers contuinue in colum 2 i.e. 51 52 53 but i want them to start again at 1 2 3 etc... how do i do this? any help is greatly appreciated well - as you have found, you can't use Autonu...

Question with UpdatePanel Partial Page postback
Could Some help: I have a page with a (gridview nested within a gridview) in an UpdatePanel (UpdateMode="Conditional"). The nested gridview drops down when you click on an arrow and then you can click on each subgrid rows command field. The gridviews are loaded from the Page_Load event. When I click on the commandfield in the subgrid the Page_Load fires during the partial page postback. From what I have read this is normal with UpdatePanels. But What I don't understand is why both Page.IsPostBack and ScriptManager.GetCurrent(Page).IsInAsyncPostBack test...

chart question #4
Can anyone please give me some guideline on how to draw a chart with y-axis is the count of the item in x-axis? I am using Excel 97. Thanks. I am not certain I fully understand your question, but I am going to give it a stab. Excel charts are very flexible, and I think you need to keep in mind that you need to have the right data in place to draw the chart. I.e. don't focus on the chart itself, focus on the data in the spreadsheet you are trying to chart, and once that is in place, your chart will look the way you want. -- ChrisJ Omaha "00KobeBrian" wrote: > Can...