sumif function #8

i have 2columns 
A1                 a2
a                    8
b                    5
a                    3
c                    2
c                     1
I am trying to sum the a2 col if a1 =a,b, or c
I was using sumif but not getting the right answer, is this the righ
function?? if so whats the format of the formul

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

0
6/10/2004 1:11:58 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
415 Views

Similar Articles

[PageSpeed] 22

Hi
try
=SUMPRODUCT(--(A1:A100={"a","b","c"}),B1:B100)

--
Regards
Frank Kabel
Frankfurt, Germany


> i have 2columns
> A1                 a2
> a                    8
> b                    5
> a                    3
> c                    2
> c                     1
> I am trying to sum the a2 col if a1 =a,b, or c
> I was using sumif but not getting the right answer, is this the right
> function?? if so whats the format of the formula
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
6/10/2004 1:22:59 PM
Frank I tried that with no success, does the dashes after "sumproduct
suppose to mean something?

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

0
6/10/2004 1:48:55 PM
Hi
yes. Just leave them :-)

--
Regards
Frank Kabel
Frankfurt, Germany


> Frank I tried that with no success, does the dashes after
"sumproduct"
> suppose to mean something??
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
6/10/2004 2:06:59 PM
Hi try:

  =SUMPRODUCT((A1:A100={"a","b","c"})*B1:B100)

   or

  =SUMPRODUCT(SUMIF(A1:A100,{"a","b","c"},B1:B100))


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_  _
^��^
 -- 


0
exceler1 (19)
6/10/2004 5:26:19 PM
Reply:

Similar Artilces:

function keys
This is a multi-part message in MIME format. ------=_NextPart_000_00F7_01CA63A4.2F980A00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I recently upgraded to windows 7. I don't know if this is the problem or what but suddenly I can't use my function number keys. does anyone have a fix for this problem?? thanks, bonnie ------=_NextPart_000_00F7_01CA63A4.2F980A00 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4....

Printing problem #8
I frequently print multiple copies of single page Publisher 2000 documents. Some of these files, though limited to a single page, are extremely large including multiple pictures inter-mixed with several blocks of text. Never the less printing usually goes very smoothly one copy printing after another with no pauses. In the case of some Publisher files, however, somewhere during the printing of the second copy the printer pauses for a full 10 seconds then resumes printing, but then identical pauses occur during the printing of each subsequent copy at exactly the same place and for the s...

Sumif Question
I need help with a sumif formula. I have two spreadsheets. On my report spreadsheet I want to use a sumif function that looks at two columns in the other spreadsheet and give me a total based on the following criteria. 1. Look at the date in F2 and compare to the date in C2. Count if the date in F2 is greater than C2. OR 2. If F2 is blank, and today's date is greater than C2, count. Do you mean =SUMPRODUCT(--((F2:F20>C2:C20)+((F2:F20="")*(TODAY()>C2:C20)))) -- HTH Bob "stevestr" <stevestr@discussions.microsoft.com...

IsIconic function
Hi all, To check whether the window is minimized or not I used function IsIconic. It is OK for almost applications but it has an problem with Excel. In Excel I create two workbooks, each workbook in a separate window. I call IsIconic function and pass the handle of workbook window which is displaying and active to it, the result which this function return is TRUE. This value is wrong because the window is displaying and is active window. Please tell me how to fix this problem. Thanks. I don't have an answer but I can tell you that some more complex software might be doing thing...

Finite Difference Spreadsheet
Have a look at the sumif statement that populates the temperature array I didn't think it would work but it seems to. Regards, Tim Fabe -- Message posted from http://www.ExcelForum.com ...

Missing Graph Functionality
In Office 2003, I used to be able to select the following when formating a data series in a graph: fill effects | shadows This would basically give me shadows on the left/rigt sides, etc. This does not appear to be available in Office 2007. Can I still make this selection? Hi, Are you sure about that selection in 2003. For a column chart the option of a Shadow is available on the Patterns tab. The Fill Effects button displays a dialog with Gradient, Texture, Pattern and Picture. But these are all fill effects and not shadows. Any way in xl2007 you would select the series and then usi...

How can I print an 11x17 page on a single 8 1/2 x 11 page?
I am using an 11x17 format and printing my pages at a Kinko's on 11/17 paper. What I need is to be able to see what it looks like on a smaller sheet which my printer can handle. Hopefully, there's some method I can use to reduce the 11/17 image to 81/2x11. John W. <John W.@discussions.microsoft.com> was very recently heard to utter: > I am using an 11x17 format and printing my pages at a Kinko's on > 11/17 paper. What I need is to be able to see what it looks like on a > smaller sheet which my printer can handle. Hopefully, there's some > method I can...

Trouble with IE 8 04-11-10
My new laptop was just working perfectly with IE 7 and Vista. Then IE 8 was downloaded automatically. Huge problelm as when I try to sign into Facebook and use Farmville or other apps it just loops to the login and insist that I adjust my cookie settings which I had tried to do numerous times but it doesn't seem to make any difference. The yellow triangle shows up in the bottom left with permission denied on several lines. What the heck does that mean? I'm not a computer techie and don't wanna be either. I just want things to work correctly when they're suppos...

"Update in Progress" for 2 weeks now, 8 different brokerage accts
I have not been able to update ANY of my 8 different brokerage accts with 3 different investment companies (Schwab, Morgan Stanley and TD Ameritrade) since 4/28. They all say "update in progress" at the Account list screen. When I click on "Read Call Summary Messages" and go to "Review Update Results" (Funny how the thing you click on doesn't have the same title as the screen it takes you to . . .) for each of these brokerages (Schwab, Morgan Stanley, & TD Ameritrade) it says: "Your last call was completed on 4/28/2008 at 7:35 PM "Your acco...

put 4 postcards on an 8-1/2 x 11 in landscape
How do I put 4 postcards on an 8-1/2 x 11 in landscape mode? On the old Publisher, I found a setting in Avery that would do it. I'm so confused now. Are there Avery templates in 2007 Publisher and if so, how do I access Avery templates to accomplish this. It's probably really simple but I haven't been able to figre it out. -- Mary Virginia There is a template for Avery postcards, 4 to a sheet. When you are at "Getting Started" enter 3263 in the "search for templates" space. Hit enter. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a qu...

Public folders: query regarding the "synchronize now" function
Exchange 2003 SP2 I have a query regarding the "synchronize now" function for public folders. Say I have a public folder called "Bob" with replicas on 3 servers (ServerA, ServerB, ServerC) If I drill down to Folders/Public Folders Select the "BOB" public folder and select the "Status Tab" on say ServerA, I see all 3 replicas listed as expected: If I right click a replica I can select "synchronize now" what does this do exactly? Does it: 1) Push updates out from the replica where you right click and select "synchronize now" ...

Embedded Function for a range of answers
I apologize because I know that someone has probably asked this before but I have tried Ecel help and have searched the forum to no avail. I want to create a formula that will give me a specific answer base upon the results of a calculation in another cell. For instance, an this isn't what I want to do, but it is the easiest way to explain it Say that a cell would have a percentage in it, I want the formula fo the next cell to result in a letter grade based upon the percentage i the aformentioned cell. So, say there is an 89% in cell A1, A2 shoul return a "B", but if there is ...

Remote Users for GP 8.0
We have a remote site with 5 users that are connected to us via a T1 point to point at 1.5mbps. We would like to have these w/s connect to run great plains. Is a vpn possible? SHould we use a terminal server? Any advantages in using citrix? Thanks John John, I would usually recommend Terminal Server 2003 for what you're describing. VPN only (without TS or Cirtix) is not supported by MS, nor advisable, even over a point to point T1 you're not going to like the performance. Citrix doesn't really offer too many advantages over TS for 5 users, but is also a viable alter...

Lookup/match function
With these functions they find the highest value which is less than or equal to the lookup value. For example A B 1 30 2 60 3 70 4 80 5 100 If I want the lookup the 'A' value of 66 it will return '2'. I want to lookup the highest value ie anything over 60 return value of 3. Anybody have any ideas ? Simon -- spgprivate ------------------------------------------------------------------------ spgprivate's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35862 View this thread: http://www.excelforum.com/showthread.ph...

Incrementing/decrementing column characters using only worksheet functions?
How can I increment and decrement column characters/letters using worksheet functions? I have a list of 5 characters corresponding to columns. The first character might be A, for column 1. How can I get the second character in the list to automatically configure itself to be B, the third C, the fourth D and the fifth E? That is, I want to set this up so that the second character is linked to the first, the third to the second and so on. That way if I change the first character from A to D then the second character in the list will automatically become E, the third will change to F, the f...

"Save As" function issues
Every time I try to use the "Save As" function in Microsoft Word, my PC tries to open a completely unrelated program (AutoManager WorkFlow 6.1 - by Cyco software). I am running Microsoft Office Word 2007 (12.0.6504.5000) SP2 MSO (12.0.6514.5000) on Windows Vista Enterprise (Service Pack 2). Is this most likely an issue with the settings in Microsoft Word, or in the AutoManager Workflow program? Are you accessing Save As with a keyboard shortcut (presumably one you assigned to it)? If so, it may be being overridden by a different assignment to that combination in Windo...

Love Potion for Miss Blandish #8
yada yada hi darmasya, glanil,bavati bharata, agydanam adarmasya, kridadmanam padarmasya, ...

No data event function
Hello. I have many reports and am trying to use a function to display a message when there is no data in the report: "Report has no data." The function seems to work in that if there is data, a report runs with the data. However, if there is no data, the report does not run but "Report has no data" does not appear to the user. I'd appreciate suggestions. Public gfReportHasNoData As Boolean Public Function PrintPreviewReport(pstrReport As String, pfPreview As Boolean, pstrWhere As String) As String 'Comments: Print or preview a report and handle errors 'ps...

Sum If Function
In one column I have different qualifiers. In another column I have data. I want to create a formula that will sum all of the date in the second column that is in the same row as the qualifier in the first column. For example, if the entry in the first column=A, then I want it to include the number in the second column in the Sum. -- bhigdon ------------------------------------------------------------------------ bhigdon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23686 View this thread: http://www.excelforum.com/showthread.php?threadid=388383 "...

Outlook Contacts #8
We have migrated addresses from GroupWise version 6 to Outlook 2002. Addresses are then copied into Outlook Contacts. When addressing a message, Outlook displays "Contacts" twice in the Select names dialog box. One of these Contacts is empty. The other has the addresses. Problem: Would like to delete the empty Contacts folder. However, it does not appear in the folder list. ...

Function help #3
dear freinds: I have some problems with this data, how can I get the results thats shows in results column; YEARS DATA RESULTS 1995 100 0 1996 90 0 1997 120 2 1998 100 1 1999 150 4 2000 70 0 2001 80 1 2002 30 0 2003 150 7 2004 140 7 for example second 150 there are 7 number smallest it thank you On 18 Dec 2004 12:32:41 -0800, adahamsheh@yahoo.com (Ahmad) wrote: >dear freinds: >I have some problems with this data, how can I get the results thats >shows in results column; > > >YEARS DATA RESULTS >1995 100 0 >1996 90 0 >1997 120 2 >1998 100 1 >1999 150 4 >...

Touble with SUMIF
Hi ALL! I am having a little trouble trying to retrive a number on th condition that another cell is greater than 0. My function I am tryin to write looks something like this: =SUMIF(N4,">0",P7+1) So if the cell N4 is greater than 0 I would like the cell P7+ inserted. But i seem to be getting a function error all the time. hope someone can shed some light on this. Gazzz -- Message posted from http://www.ExcelForum.com Hi use the IF statement: =IF(N4>0,P7+1,"") -- Regards Frank Kabel Frankfurt, Germany > Hi ALL! > I am having a little trouble trying to re...

Table tag refuses to obey the CSS text-align property in non-compatibility mode in Internet Explorer 8
I have several locations on my site that have tables inside elements such as divs that specify the text-align:center; CSS property. When IE8 is not in compatibility mode, this is ignored by tables that are children of the tag the property is specified in. I have only noticed this with table tags. Here is a simple page I have written to demonstrate this: <html xmlns="http://www.w3.org/1999/xhtml"> <head><title></title></head> <body> <div style="text-align:center;"> <table><tr><td>Table</td&...

TABLE Function/Array HELP!!!
{=TABLE(,B13)} Can anyone tell me what this array function is doing and how it works? Any help is greatly appreciated! It is not an array function. The cell is part of a Data Table. You create Data Tables (in Excel 2003 and before) in the Data>Table menu command. In Excel 2007 it is in the Data tab, Data Tools block, What-if analysis drop down. Data tables should not be confused with Tables in Excel 2007. -- Kind regards, Niek Otten Microsoft MVP - Excel "chris" <csnishimoto@yahoo.com> wrote in message news:0ba2e359-df45-4c9d-9216-2aa091f83ef7@u12g2000prd.googlegroup...

GROWTH function falls over..
Hi All I am using the GROWTH function to calculate the y-axis intercept (i.e. value of y at x = 0) for an exponential function where I have four (x,y) values. This works fine, but if the user goes back and deletes one of the input data points (say if they decide it is a rogue point ...or just do it out of mischief!) the function fails (returns #VALUE). I have a trendline based on the extrapolated line which then also falls over. I get the same result if I calculate the slope and intercept using LOGEST and then use the formula to calculate y at x=0. Any way around this? I would like to instru...