what is the formula please??

If a8:a1000 contains dates and f8:f1000 contains an X if the item i
over due, can you tell me the formula to show a running total of X tha
have a 2004 value in column A on Sheet 2 please.

Any help would be most welcome.

Many thanks

Dav

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

0
4/22/2004 3:39:40 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
405 Views

Similar Articles

[PageSpeed] 43

Dave,

use the following array-entered (use ctrl-shft-enter not enter whe
entering the formula)

=SUM((YEAR(A8:A1000)=2004)*(UPPER(F8:F1000)="X"))

or use this formula just normally entered.

=SUMPRODUCT(INT(YEAR(A8:A1000)=2004),INT(UPPER(F8:F1000)="X")

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

0
4/22/2004 4:24:58 AM
Thanks very much

Dave :cool

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

0
4/22/2004 4:32:40 AM
If I understand you correctly:

    =SUMPRODUCT(--(YEAR(Sheet1!A8:A1000)=2004),--(Sheet1!B8:B1000="X"))



In article <p3project.153hw3@excelforum-nospam.com>,
 p3project <<p3project.153hw3@excelforum-nospam.com>> wrote:

> If a8:a1000 contains dates and f8:f1000 contains an X if the item is
> over due, can you tell me the formula to show a running total of X that
> have a 2004 value in column A on Sheet 2 please.
0
jemcgimpsey (6723)
4/22/2004 4:39:14 AM
Reply:

Similar Artilces:

Help with QSI Import utility please
Greetings. Has anyone encountered strange/erratic behavior with the QSI Import utility? Anybody know what this (03021) error code is and what's the typical root cause?? I'm consistantly getting these 00003021 error's for no apparent reason ~ they'll usually show up on the first 10 rows of my .csv data (exported from excel 2003) -- even from totally different data sources...even though the data is fine (I tested by appending the same 10 lines of data that had errored out in the beginning of the file to the end of the import file and it'll take it there w/out any er...

Entering array formula using VBA
The following is an array formula.... =IF(OR(G26="",VLOOKUP(G26,$G$16:$H$23,2,FALSE)=0),"",MAX(IF($G$16:G26=G26,$H $16:H26)+1)) 1. Is there a way to enter this into the cell (G26) using VBA (as an array)? 2. If so, if the row that this is to be entered is diff to row 26, how does this get entered into say, G35 and the references changed appropriately? Rob Hi Rob, try this Sub rob() Range("F26").FormulaArray = "=IF(OR(G26="""",VLOOKUP(G26,$G$16:$H$23,2,FALSE)=0),"""",MAX(IF($G$16:G26=G 26,$H$16:H26)+1))" ...

Help Please revert file
I am running Excel 2000. I opened a file up and did modifications. I then wanted to save as another file unfortunately I hit the save button. Is there any way I can get my old file back. Ron Hi Ron, The unfortunate answer is that unless you have a backup copy or suitable recovery software installed, you will not be able to undo the changes. -- Regards, Norman "Ron Gibson" <aspexpert@comcast.net> wrote in message news:%23x0nLYW$EHA.3416@TK2MSFTNGP09.phx.gbl... >I am running Excel 2000. I opened a file up and did modifications. I then > wanted to save as an...

Please help me...its VERY Urgent
Hi to all I have a problem taht is like... I make a customize MS agent character with the help of MS character agent editor. Now i want to integrate this agent with my VC++ application but i am not getting how can i code to integrate it or how VC++ take a .acs files to display that animation or agent? its very urgent plz help me as soon as possible. "Adroit" <sweet.sapna9@gmail.com> wrote in message news:94f95c49-4db4-47a3-b0b2-0d87284ff066@j28g2000hsj.googlegroups.com... > Hi to all > > I have a problem taht is like... > ...

Sorting numbers, please help?
I ahve a spreadsheet that contains 5 columns of whole numbers rangeing from 1 to 100. I need to count how many times each number appears in each column or even in all 5 columns together. Ex. the number 1 appears 252 times, etc. Does anyone know how to sort the data that way? I have imported into an access table as well if that's easier. Please help? you could use CountIf function on excel. e.g CountIF("A1:A500", 1) which will should how many time 1 appear from range (A1 to A500). "Kevin Schultz" wrote: > I ahve a spreadsheet that contains 5 columns of w...

Formula copying from one workbook to another...
Hello Excel community friends. Here's my problem: I trying to copy a formula from a worksheet in one workbook to a worksheet (of the same name and same cell location) in another workbook. Say the formula from workbook1/worksheet1 is =sum(1+1) and the name of workbook1 is "Me" and the name of the worksheet1 is "Sheet1" and located in cell A1. Of course, when you copy the formula from "Me" to another workbook (named, say, "You") and to the same location (A1) and on the same named worksheet, "Sheet1", the preface of the formula is s...

Output to Excel Coding Suggestions Please
I have a subform where when you double click on a specific record, another form opens up with specific information for the record selected. This form is a pivot table. What code would I use to automatically output that form (pivot table) to Excel. I tried using a Macro, however I get an error message saying my version of Access (2003) does not have that feature. I am trying to output this into a template file already created with the proper formatting. This is the code used on the double click to open the pivot table; its works fine. Private Sub ProjectID_DblClick(Cancel As Integ...

A little help please on a macro to display results.
I have the code listed below that searchs a folder and if it finds any PDF files its displays them with a hyperlink in the spreadsheet. My problem is that under the MAIN folder I have tons of files now and its very hard to find the PDF I am looking for.. I redid my file structire under main and now I have folders for years 2010, 2012,2013,2014,2015 and so on. Under each years directory are the directories for the 12 months of the year & under each of these months I have two folders A-cast & B-cast. What I would like is that the code could search the folders starting for...

querie based on fields from table, please help
I have a form with about 40 buttons on it, and each one opens a report based on a query. What I am trying to do is code the buttons so that there is only one query and one report (since the format is the same) that they pull from, as opposed to 40 different reports and queries so I dont have so much to update when something changes to a report format. The queries are based off different fields in the table. (cmd1 opens rpt1 based on qry1, cmd2 opens rpt2 based on qry2, etc.. qry1 is based on fld1 of tbl1, qry2 is based on fld2 of tbl1, etc. .) I was thinking of something like oncli...

Help Please: Transferspreadsheet brings in odd format
I'm using Ac 2003, Win xp pro, Excel 2003. My sheet is formatted as text. Example: data is 3249388005 in Excel but imports as 3.24939e+009 in Access. I've formatted the target table to which the data is appended as Text. As both the Excel cells and the Access field is formatted as text, I don't know how to correct this. It appears this is a scientific notation type data format problem. Please help: how to I preserve the Excel format? Perico <Perico@discussions.microsoft.com> wrote: >I'm using Ac 2003, Win xp pro, Excel 2003. My sheet is formatted as text....

Help pLease
Hi all, Any chance to change the one of the field value (appro 500 records) in an expired contract. the chaging value is from the same contract form field. Advance Thank you for your support Sujith Well, this is not a supported way of doing it. you can probably go to the MSCRM database to change the value for that field. This field should be in the ContractBase or ContractExtensionBase table. Warning: do it on your own risk. before you do it make sure you backup your CRM databases just incase. Darren Liu Crowe Chizek and Company http://www.crowecrm.com On 3=D4=C222=C8=D5, =CF=C2=CE=E71=...

Crystal Reports formula to Access
Any suggestions on how to use this formula in Access? if not(IsNull({MASTER.JMT_DATE})) then "Post Judgment"else if (IsNull({MASTER.JMT_DATE})) and not(IsNull({MASTER.SUIT_DATE})) then "Post Suit, Pre Judgment"else if (IsNull({MASTER.SUIT_DATE})) and (IsNull({MASTER.JMT_DATE})) then "Pre Suit" Here is a guess -- IIF([MASTER].[JMT_DATE] Is Not Null, "Post Judgment", IIF([MASTER].[JMT_DATE] Is Null AND [MASTER].[SUIT_DATE] Is Not Null, "Post Suit, Pre Judgment", IIF([MASTER.SUIT_DATE] Is Null AND [MASTER].[JMT_DATE] Is Null, "...

How to use "Creat Rule", help please.
I use Office 2003, on Outlook's toolbar has "Creat Rule" button, I do not understand how to use "Creat Rule". How's the details of using this "Creat Rule", please help. Thank you. seagulls You can create a rule to automate some tasks when you send/receive e-mail like move it to a specific folder to automatically sort your e-mail. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "seagulls" <anonymous@discussio...

I want to learn Macros in excel, please let me know about it
See http://www.mvps.org/dmcritchie/excel/getstarted.htm ...

Unable to load Plug-in assembly
Hi, while trying to ‘Save’ (or ‘Save and Close’) a contact after changing their last name I get an error message "Unable to load Plug-in assembly". Can anyone help me on this. This is urgent please. thanks Karthik Do you have a Plug-In? Was this an upgrade from 3.0 to 4.0? -- Fortis www.webfortis.com "Karthik-" wrote: > Hi, > > while trying to ‘Save’ (or ‘Save and Close’) a contact after changing their > last name I get an error message > > "Unable to load Plug-in assembly". > > Can anyone help me on this. This is urgent ple...

Copying and adjusting formulas
Hi Firstly thanks for many tips picked up in this ng - I've learnt a lot. Copying and adjusting formulas 26 sheet workbook (Excel 2000) with a sheet for each weeks figures broken down departmentally and totalled for the week. From sheet 2 onwards the sheet has year to date being current weeks figures + previous year to date. This was originally in .123 and as you copied from one sheet to other sheets the formula adjusted the sheet name - in Excel it keeps the formula without adjustment. I find this weird (and very long winded as each sheets formula will need changing). Am I missin...

Formula question -simple for those who know
Hello I'm trying to add a number that was calculated by means of a formula in one column to the sum of a different column. Example: Column A (running balance) Column B (sum) =+e5+c6-d6 =sum(h1:h8) Col A's number is running balance. I copied the formula down so that the next row changes the formula to =+e6=c7-d7 I want that running balance to add to a sum in another column. I can add the first cell to it, but it doesn't change if the running balance changes. I know that is because it's pointing to that single...

Current Views........Help Please!!!!!
I have one guy that keeps loosing columns in his inbox (ie. subject\received columns). I know he isn't going through the proper way of changing views and editing these features. (ie right clicking and customizing current views). Is anyone aware of any key strokes or something else that he might be doing to removed these columns. HELP PLEASE! ...

help with Disabled code please
Hello All ! Was just wondering if someone can help me with a little code. I know bits of what I want to do but getting a bit confused... Basically I have two forms, on one form is a button that opens up the second form using... On_Click DoCmd.OpenForm "Jobs", , , , acFormAdd what I would also like it to do is from that button only that when the second form opens certain buttons and combos are disabled. I want this to only happen from the button and not if the 2nd form is opened on its own without the 1st form. Im guessing that I need to add additional code to the on click part ...

HELP, please!! Outlook not opening; "File for InfoPath Unavailable in Windows Installer (Error 1605)"
I am using Office 2007 Standard, with Windows Vista Business. Each time I attempt to open Outlook 2007, I receive a pop-up informing me that InfoPath is not available, due to a missing file in Windows Installer (Error 1605). I cancel out of the pop-up, but Outlook opens in an unstable form ("not Responding"). After a couple of resets of Outlook, it finally opens properly. I have searched all applicable Microsoft websites for help, but can find no successful solution to the problem. Any suggestions? I would be most appreciative of any help. THANKS!! On Aug 18...

overlaying formulas?
I'm not familiar with the proper terms, but I am trying to collect information in a schedule I have created. Is there a way to have three different totals for count only from the same three vertical columns. I will be glad to explain further, if someone can help. I may not be asking in the proper terms. Thanks for any help. Give more specifics, like column letters and row numbers and what you want to count. ************ Anne Troy www.OfficeArticles.com "Charlie" <Charlie@discussions.microsoft.com> wrote in message news:00BDEBCD-CA3E-49E0-B0EC-328A1E4EB74E@microsoft...

Formulas and sorting
I've put formulas in eacg column C cell to get me the percentage of th column B total in each cell of column B. Formula is B1=sum(B1:B10) and this works but I can't sort and retain the proper percentage. Ther must be a way to do this that would allow me to sort and maintain th proper value. Any help -- Message posted from http://www.ExcelForum.com You need to make the reference to B1:B10 absolute. Use: =B1/SUM($B$1:$B$10) HTH Jason Atlanta, GA >-----Original Message----- >I've put formulas in eacg column C cell to get me the percentage of the >column B total in each...

Help with formula 04-20-10
Hi, I hope you can help me with this formula. I have two spreadsheets called the following:- 1) SME main 200410 2) SME PRE accounts CAT1_RCB checked 120310 (2) Coloumn A in both spreadsheets contains a list of numbers (e.g. 1012381211420) and both coloumns contain 6905 rows. I need a formula in cell b1 of spreadsheet 2 that will look at the number in cell a1 of spreadsheet 2 and if that number is in coloumn A of spreadsheet 1 then I want cell b1 of spreadsheet 2 to return a value of Yes if not then I want a value of No Thanls It's easier to set this up with bot...

Error: Value in Formula of wrong data type
I have a worksheet that uses custom formulas on several inputs from that page. I've used it for a while and never had a problem, but made some changes elsewhere in the worksheet (not linked to this part) and now on many of the cells using these formulas, I get a '#VALUE!' error, stating 'Value in Formula of wrong data type.' The data type is the same it has always been, so I don't know why it's not working now (formula just uses if thens to compare different numbers and return values like 0, 1, or 2). What's even more perplexing is that several ...

Formula bar
Hi All, Is there any way (I hope so) to set the maximal size of the formula bar? I have large formulas and the expanding formula bar is simply hiding half of my sheet and a lot of cells I need to see. Please advise (no, writing smaller formulas is not an option :) Thanks Marton No, it is no adjustable AFAIK. Why is not writing smaller formulae an option, you can put intermediary steps in hidden columns, it also makes debugging easier. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "VilMarci" <dontsend@here.mail> wrote in mes...