Sum IF (x and Y)

how do I create a formula to get a SUM if two columns are 
utilized, in other words, sum if AREA(columnA) is 10, and 
WORK(columnb) is CONSTRUCTION...
How can you tyoe the formula...

thanks 
Juan
0
7/24/2003 6:27:22 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
503 Views

Similar Articles

[PageSpeed] 8

Ah, but what if he has entries where he has CONSTRUCTION in B and a number other than 10 in A.  He
would get them counted as 10 well.

=SUMPRODUCT((A1:A100=10)*(B1:B100="CONSTRUCTION"))*10  will hopefully do it though

--
Regards
           Ken.......................    Microsoft MVP - Excel
                   Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
  Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message
news:OkT#sJhUDHA.1196@TK2MSFTNGP10.phx.gbl...
> Juan,
>
> I fear I may be misunderstanding but from what I read of your post, you
> could just count the instances of CONSTRUCTION and multiply by 10
>
> =COUNTIF(B:B,"CONSTRUCTION")*10
>
> --
>
> HTH
>
> Bob Phillips
>
> "Juan" <jgomeztagle@hotmail.com> wrote in message
> news:00bd01c35211$390beb70$a501280a@phx.gbl...
> > how do I create a formula to get a SUM if two columns are
> > utilized, in other words, sum if AREA(columnA) is 10, and
> > WORK(columnb) is CONSTRUCTION...
> > How can you tyoe the formula...
> >
> > thanks
> > Juan
>
>


0
ken.wright (2489)
7/24/2003 6:49:05 PM
mental blockage. Knew I wasn't getting it.

Bob

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:OaUTDRhUDHA.1172@TK2MSFTNGP11.phx.gbl...
> Ah, but what if he has entries where he has CONSTRUCTION in B and a number
other than 10 in A.  He
> would get them counted as 10 well.
>
> =SUMPRODUCT((A1:A100=10)*(B1:B100="CONSTRUCTION"))*10  will hopefully do
it though
>
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                    Sys Spec - Win XP Pro /  XL2K & XLXP
>
> --------------------------------------------------------------------------
--
>   Attitude - A little thing that makes a BIG difference
> --------------------------------------------------------------------------
--
>
>
>
> "Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message
> news:OkT#sJhUDHA.1196@TK2MSFTNGP10.phx.gbl...
> > Juan,
> >
> > I fear I may be misunderstanding but from what I read of your post, you
> > could just count the instances of CONSTRUCTION and multiply by 10
> >
> > =COUNTIF(B:B,"CONSTRUCTION")*10
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > "Juan" <jgomeztagle@hotmail.com> wrote in message
> > news:00bd01c35211$390beb70$a501280a@phx.gbl...
> > > how do I create a formula to get a SUM if two columns are
> > > utilized, in other words, sum if AREA(columnA) is 10, and
> > > WORK(columnb) is CONSTRUCTION...
> > > How can you tyoe the formula...
> > >
> > > thanks
> > > Juan
> >
> >
>
>


0
bob.phillips (411)
7/24/2003 6:56:14 PM
Reply:

Similar Artilces:

x-axis scaling
I am trying to create a plot that uses x-axis values of time. Most data points are in one-hour increments, but a few are only minutes apart (e.g. - 1:00; 2:00; 3:00; 3:01; 4:00). The x-axis scaling assigns them all equal charting values (i.e. - they all reside an equal distance from one another). Is there an easy way to assign their actual values to the chart? Which chart type? Which EXCEL version? "todd" wrote: > I am trying to create a plot that uses x-axis values of time. Most data > points are in one-hour increments, but a few are only minutes apa...

Run a macro x time in a row
Hello there, another dumb question. I want to run the macro x times in a row, lets say 10 times for instance, what code should I add ? Thanks a lot for your help Daniel Hi Daniroy, Try something like: Public Sub Tester() Dim i as Long For i = 1 to 10 Call MyMacro Next i End Sub --- Regards, Norman <daniroy@gmail.com> wrote in message news:1123227839.434444.189490@g14g2000cwa.googlegroups.com... > Hello there, > > another dumb question. I want to run the macro x times in a row, lets > say 10 times for instance, what code should I add ? > &g...

How to sum with 2 conditions
In the following example how can I add all the comissions for Apartments in San Francisco or Houses in New York using SumIf for example?? Type Property Value Commission Apartment San Francisco 7,000 House New York 14,000 Apartment New York 21,000 Apartment San Francisco 13,000 House San Francisco 28,000 Apartment New York 8,000 A simple way would be to use =sumif+sumif -- Don Guillett SalesAid Software donaldb@281.com "Alejandro" <Alejandro@discussions.microsoft.com> wrote in message news:29970A48-CF9C-4D04-8C65-...

Sum items occurring in a month and year
I've got the following formula to sum referrals received in a particular month. Is it possible to make it specific to the year as well as the month? =SUM(IF(Referrals!$D$4:$D$10002='Referrals List'!$A5,IF(Referrals!$J$4:$J$10002=MONTH($B$2),1,0))) Take a look at SumProduct instead... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Saylindara" wrote: > I've got the following formula to sum referrals received in a particular month. > > Is it possible to make it specific to the year as well as the month...

Click-2-run virtualization handler error X 30015
how do eliminated or reinstall office 10 beta with the above error Bonjour ??Suite a un telechargement Plusieurs mois a ce jour le logiciel Micrsoft Outlool 2010 (Beta) ne fonctionne plus. Je rencontre a Chaque fois, je LORSQUE Redemare lun. ordinateur Les erreurs echec de la configuration du click-2-Run Suivantes Erreur 30015 click-2-Run virtualisation Handler je ne comprend pas se qui est arriv? je vais dans le panneau de configuration pour d?sinstaller le logiciel, imposible de terminer l'op?ration, r?essayer la, si le probleme persite contacter le support technique Microsoft...

SUM OF PAYABLES DUE ON A GIVEN DATE OR RANGE
I HAVE AN ACCOUNTS PAYABLE WORK SHEET. IN COLUMN G ARE THE DATES THAT A PARTICULAR PAYABLE IS DUE, i.e. 30-APR. IN COLUMN H ARE THE AMOUNTS DUE FOR A PARTICULAR ACCOUNT, i.e. $1000.00. IN CELL F62, I NEED A FORMULA THAT WILL SUM UP THE TOTAL AMOUNT DUE ON 30-APR. SO I KNOW HOW MUCH CASH IS NEEDED ON THAT DAY TO COVER THOSE PAYABLES. IN CELL F63, I NEED A FORMULA THAT WILL SUM UP THE TOTAL AMOUNT DUE FROM 25-APR TO 30-APR SO I KNOW HOW MUCH CASH IS NEEDED DURING THAT PERIOD. If your dates due are in Column G and corresponding amounts due are in Column H, then, in Cell F62 insert the form...

Office 2004 Free upgrade for previous Office v. X users
When I bought Microsoft Office X I was told that once Office 2004 for Mac were release I could get a free upgrade to it since I bought it few months before the release. I have looked all over to find a way to do it but I have not found any info about it. Can any one help me on this matter? Where can I download the form if this is true or how can I get the Free upgrade? Thanx in advance In article <d9ec8b19.0408192014.41edb293@posting.google.com>, oiciruam111@hotmail.com (Mauricio O) wrote: > When I bought Microsoft Office X I was told that once Office 2004 for > Mac were releas...

Counting the number of "Y" in a row
Hi all I know this has to be pretty simple but I can't quite figure it out. I want a field at the end of each row which will count the number of fields with a "Y" and show the total number. I tried an IF statement but this went all wrong and I have tried the COUNT () statement but was not sure how to get this to count letters. Can somebody please help? Have a look in the help index for COUNTA -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Mikey C" <m.cringle@gmail.com> wrote in message news:1173871396.451963.103300@p15g2000hsd.googlegroups.com....

Multiple data in X-Axis
Hi everyone, I'm trying to chart the following spreadsheet layout: Expr2 FinancialClass SumOfTxnAmount January, 2003 M $145 January, 2003 C $1432 January, 2003 P $23 January, 2003 S $432 January, 2003 B $2432 January, 2003 F $321 February, 2003 P $321 February, 2003 M $542 February, 2003 C $542 February, 2003 S $987 February, 2003 HMO $567 March, 2003 M $56 March, 2003 C $3456 March, 2003 B $526 March, 2003 P $1665 March, 2003 S $363 March, 2003 HMO $245 March, 2003 PPO $248 March, 2003 F $166 And I need the X Axis of the bar chart to look something like this: | ...

sum #3
When I try to calculate the sum of special cells in a row, which are copied as link fom another worksheet, the result is zero. I tried to unlock the cells, also confirmed the format of the cells as numbers. But still the result is the same. Then I copied and pasted the values of the cells in a new worksheet but still the result is zero , that should be around 100,000 Can anyone please help me? Faran Just because the cells are formatted as numbers does not make them numbers. Copy a blank unused cell, select the range of 'numbers' and Paste / Special / Add. This should make your 'n...

2 y axis, bar and line graph question
I am plotting 2 series of data against time. One series needs to be a line and the other a bar. I got it pretty close with the 'custom types' (built in)-- 'line - column on 2 axis'. However, this will only let me have the primary y-axis as the bar graph. Is it possible to customize this further so that the primary y-axis is the line and the secondary y-axis is the bar graph? Thanks, please help. Kyle ...

Graph displaying dates alphabetically on x axis, not by date
Hello! Title says it all - I want the x axis to show dates in DATE ORDER, not alphabetical order. I've read thru the forum, can't see how to fix it - your help will be appreciated. Kind Regards, Russell. p.s. Here is the graph Row Source, should there be an ORDER BY statement in here? SELECT (Format([Date Paid],"mmm"" '""yy")) AS Expr1, Sum([Tenant Payments]. Amount) AS SumOfAmount FROM [Tenant Payments] GROUP BY (Format([Date Paid], "mmm"" '""yy")), [Tenant Payments].[Date Paid], (Year([Date Paid]...

Summing visable cells only
I have a worksheet with 8000+ rows. Some of the rows are hidden. I want to have a formula at the bottom of the rows that sums only the visable cells (non-hidden) rows. Any suggestions? (The rows are hidden and not filtered). -- Thanks Buuuuddddy! =SUBTOTAL(109,A1:A8000) will give you what you need. -- Regards, Dave "Paully Shore" wrote: > I have a worksheet with 8000+ rows. Some of the rows are hidden. I want to > have a formula at the bottom of the rows that sums only the visable cells > (non-hidden) rows. Any suggestions? (The rows are hidden and not fi...

Sum IF function on timesheets
I am designing a timesheet to total hours people work each day into standard and overtime hours. Users put the following variables into a sheet: Project worked on, day of the week, number of hours. Often people will work on more than one project each day. I want to auto calculate the number of hours in total for monday - sunday with the first total being the standard number of hours in total on the day (7.5) and the 2nd being the total above 7.5 ie overtime worked. So Joe Bloggs works Mon Project 1, 3.5 hours, Project 2, 4 hours and Project 3, 2 hours. He has worked 9.5 hours in the day. ...

summing number of 'y's in a filtered column
Basically I have a spreadsheet which is filtered by column R and I wan to sum the number of 'Y's in column P based only on the visibl filtered table rather than hidden rows. Now I know the subtotal worksheet function will ignore hidden rows however it does not seem to be able to total up the number of 'Y's i the range of cells. The count function would sum up the number of 'y' but it counts the hidden columns as far as I know. Is there some other function which would allow me to sum the number o 'y's ignoring hidden rows -- neowo ---------------------------...

S.O.S. M A Y D A Y !!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Hello! I'm a stand alone mother, I'm unemployed. My little childrens are cancered. They are twins.We are living in horrify misery .We have no money for food, medicine, heating and medical operation .The operation is the most important it's cost $3.000 If you want to help us send money to the following bank account : HU85119911198222607100000000 Thank You ...

How do I plot "Month, Year" on the x-axis for prior to 1900?
I was hoping this would be easier in the latest Excel, but I had the same problem with the other versions. The date system seems to begin at 1900 and doesn't recongnize dates prior to that. In my field, I have data that goes back several decades before 1900. Also, I would like the date to show up on the x-axis as "Month, Year" and I would like to have the option of displaying the data in my chart from a specified minimum and maximum date without having to change the selection of my source data (I know I can do this if the x-axis is just numbers instead of dates, but I wan...

Memory failure : Getting Sum to work
I added the number 200 3 times (vertical) . Got the correct answer but i can't seem to get the answer to appear in the total cell. Just the formula shows up . What am i doing wrong ? Format the cell with the formula as General (or number--just not text). Then with that cell selected, hit F2 followed by enter. Drew Cutter wrote: > > I added the number 200 3 times (vertical) . Got the correct answer but i > can't seem to get the answer to appear in the total cell. Just the > formula shows up . What am i doing wrong ? -- Dave Peterson It sounds as though the cell...

customize label on X axis
Hello I'd like to draw a chart with measures done on a non-regularily basis. I can make it with a single curve using a chronological axis in order to have a distance between two points proportionnal to the elapsed time between them. My problem is that Excel displays under the X-axis dates that are separated by an uniform time. I'd like to see only the dates corresponding to the measures. And I'd also like to have a vertical grid corresponding aligned with the points ant not drawn with the same interval. Does someone know a way to do this (if it's possible) ? I must add that I...

Line Chart
I am trying to create a line chart but am having trouble with labeling the X-Axis. The graph is charting temperature on the y-Axis and the date and time of the testing is running along the X-Axis. There are at least 1096 time entries and I would like the X-axis labels to only display the date which is only two days and not the time (reason for all the entries). The X-Axis is currently displaying the time at a 90 degree angle and only one date at an 180 degree angle, becuase of the numerous time entries the second date does not show up along the X-Axis. Please help. Use an...

update won't run...from v.X to 10.1.2
I have recently installed Office v.X on my office G4 and am trying to upgrade so I can connect to our exchange server. I downloaded the 10.1.2 updater and have run it but it seems to hang on the "Gathering Information" window with the spinning beach ball of death. I have tried restarting, repairing permissions, redownloading the installer and nothing chanages. Am I doing this the right way or is there an upgrade I'm missing somewhere? is there something else I'm not doing right? Thank you in advance for your help!! peace jarod Hi Do you have some mounted serve...

Get Excel to provide equation for my x and y
I am doing an assignment on how hot water gets in degrees celcius when so many kilojoules are supplied to it. i have a graph, with data provided to me for this function. kilojoules is my y, and degrees celcius is my x. i need to get excel to provide an equation that relates heat, y supplied to temp., x, in the range of 0-100 degrees celcius. In A1 type the word temperature; in B1 the word kilojoules Under these enter your data Put the mouse pointer anywhere with in the data; use the Chart Wizard on the toolbar to make an XY chart with just markers (no line) When you have the chart, right...

Reinstalling Microsoft Office v.X
- Downloaded trial version of Microsoft Office, - Forty day trial period came and went - Did not want, - Cleanly removed same, - Attempted to access both Word and Excel (Microsoft Office v. X) - Question mark superimposed over each icon and could not access either program, - Reinstalled program,(treated as new owner) - New product I.D. assigned, - Attempted to register on-line, - Message, "Unable to service your request". - Contacted Microsoft - No support as of January 01 / 07. What is the process to reinstall my original Microsoft Office v. X? Hello George - There's no need...

Summing entire column
I have a 2 worksheet workbook that is being used as a Credit Card log. The user enters all the transactions into a worksheet named, CreditCardLog. The CreditCardLog worksheet takes 10 values, PONum, PODate, Vendor, Description, Amount, CCCObjectCode, C, CO, CR, PCO. The totals are then displayed on the first worksheet, Totals. This adds up C, CO, CR, PCO columns for totals. These sums are done like this, =SUM(CreditCardLog!H2:CreditCardLog!H65536) The issue that I am having is that if a user inserts a new line or pastes some content into the CreditCardLog worksheet, then the sums o...

Framework X error
I am getting this message when I open Office X programs: "An unexpected error occured while trying to load the Microsoft Framework X library" There are users on the ibook. This happens under student where much is locked out. I checked permissions for the user data folders in the users Document folder and these are ok. Any Ideas? Thanks in Advance norm On 8/6/03 1:08 PM, in article 6c46d36e.0308061208.4faa16ce@posting.google.com, "Norm" <gwaltnen@mail.ips.k12.in.us> wrote: > I am getting this message when I open Office X programs: > > "An unexpecte...