function to check for entry

I'll try and make this as clear as I can...

I am looking for a function that will check column b,c,d,e for an entr
if there is an entry then check row n in the same row for it's value.

that's the first bit, then I need to be able to check the date valu
(month) in column a and total it for the month.

perhaps I could run the first function only looking for month=1 o
something and just run it 12 times?

not sure how to go about this

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

0
6/29/2004 10:52:11 AM
excel.misc 78881 articles. 5 followers. Follow

12 Replies
578 Views

Similar Articles

[PageSpeed] 59

well I've got this far but it still doesn't work and gives an error:

=IF(MONTH(A3:A167)=4,IF(B3:B167>0,SUM(O3:O167)), 0)

what I am trying to make it say is:

search through B3:B167 if there is an entry check if the month is apri
and there a value in the corresponding cell in column O.
I want to get the sum of column O that fit the criterior define
above.

I'm propbably miles away from it with that function

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

0
6/29/2004 3:42:25 PM
this does what I want but only in one line:
=IF(MONTH(A30)=4,IF((B30>0),O30,0), 0)

I want to be able to check instead of the individual cell, the arra
3:16

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

0
6/29/2004 4:17:40 PM
Can you put a small sample of your data in a post

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

0
6/29/2004 4:32:34 PM
a rough sample of possible variables.

___A______B__ C________
1]  21/4/04     100         17.5
2]  28/4/04
3]  27/5/04     200         35
4]  17/4/04     50
5]  14/4/04     15
6]  13/2/04     100         17.5
7]  16/4/04     100         17.5
8]  29/7/04     7

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

0
6/29/2004 4:49:34 PM
a rough sample of possible variables.

___A______B__ C________
1]  21/4/04     100         17.5
2]  28/4/04
3]  27/5/04     200         35
4]  17/4/04     50
5]  14/4/04     15
6]  13/2/04     100         17.5
7]  16/4/04     100         17.5
8]  29/7/04     75


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

0
6/29/2004 5:01:31 PM
a rough sample of possible variables.

___A______B__ C________
1]  21/4/04     100         17.5
2]  28/4/04
3]  27/5/04     200         35
4]  17/4/04     50
5]  14/4/04     15
6]  13/2/04     100         17.5
7]  16/4/04     100         17.5
8]  29/7/04     7

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

0
6/29/2004 5:06:46 PM
a rough sample of possible variables.

___A______B__ C________
1]  21/4/04     100         17.5
2]  28/4/04
3]  27/5/04     200         35
4]  17/4/04     50
5]  14/4/04     15
6]  13/2/04     100         17.5
7]  16/4/04     100         17.5
8]  29/7/04     7

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

0
6/29/2004 5:09:26 PM
a rough sample of possible variables.

___A______B__ C________
1]  21/4/04     100         17.5
2]  28/4/04
3]  27/5/04     200         35
4]  17/4/04     50
5]  14/4/04     15
6]  13/2/04     100         17.5
7]  16/4/04     100         17.5
8]  29/7/04     7

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

0
6/29/2004 5:44:23 PM
a rough sample of possible variables.

___A______B__ C________
1]  21/4/04     100         17.5
2]  28/4/04
3]  27/5/04     200         35
4]  17/4/04     50
5]  14/4/04     15
6]  13/2/04     100         17.5
7]  16/4/04     100         17.5
8]  29/7/04     7

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

0
6/29/2004 5:57:57 PM
whoops! sorry about that

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

0
6/29/2004 5:59:38 PM
There's probably a better answer, but one solution can be to create 
column (say, column P)  that references column A with the functio
month().

Then, you can just do:

=sumif(column P, 4, column B)

then repeat it for each column that you're interested in and for eac
month.

By the way, I'm having problems using month() for the dd/mm/yyy
format.  Does it work for you

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

0
6/29/2004 7:03:23 PM
Hey I've got it.

=SUMPRODUCT(--(MONTH(A3:A167)=4),--(B3:B167>0),O3:O167)


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

0
6/29/2004 9:22:14 PM
Reply:

Similar Artilces:

Calculating more than one value in a function
Hi all, Can something like this be done: Cos(A1:A10)? That is calculate the value of Cos(A1) through Cos(A10). If so, how? I keep gettting an error. Lurch The trick is to first select for instance B1:B10, then in the formula bar put your formula and enter it with ctrl + shift & enter -- Regards, Peo Sjoblom "Charlie Johnson" <cj-bubba@bite.mindspring.com> wrote in message news:WpNub.10411$Wy4.3919@newsread2.news.atl.earthlink.net... > Hi all, > > Can something like this be done: Cos(A1:A10)? That is calculate the value > of Cos(A1) through Cos(A10...

Pivot table, IF function, calculated item versus calculated field
Trying to make my pivot tables more useful by customizing my own formulas. I would prefer to use the following formula within a pivot table but can't seem to get the result I want. I'll add it to the source data if I have to but suspect my lack of pivot table knowledge is the problem. Assume Source data is Description Title Period Amount apples Miss 12 1000 pears Miss 12 500 geoff Mr 12 6000 able Mr 36 600 Formula I would add a column Yearly to the source data =if(period=36,0,Amount) and then t...

Is there some type of "PRE-FETCH LIKE" Function ?
I've got a query that is based on a massive SQL table (use an ODBC link to the SQL table). The query works fine. However....it seems unnecessarily sluggish when scrolling down the list of returned records. While the average query may return a list of around 100-400 records......when I begin to scroll down through them.......there are long pauses before below entries will appear....like the query is processing on an as-needed basis. But...since it already indicates how many records were returned (usually less than 400) at the bottom of the query window...you'd think any needed proce...

How to check authorisation
Good afternoon, Is it possible to have a checking on the computer name and user name when certain a database is being accessed. i.e. I only want the work done by certain person and on certain machine. If this is possible where can I place this security check and how. Some coding would be very helpful. Regards, Smiley For the computer name: http://www.mvps.org/access/api/api0009.htm for the login user name: http://www.mvps.org/access/api/api0008.htm -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Smiley" <firewo...

Checking for ALL Upper case?
Can anyone let me know how to check a cell for text in all upper case? I have a column containing only text, some cells contain the manufacturer & some the model. However the Manufacturer is in all upper case. And the cells below that particular Manufacturer are the models of that manufacturer. So I am wanting to check for all upper case so I can get the related manufacturer in the cell to the left of the model field, using an IF statement. What can I incorporate into an IF to check a cell for an all upper-case text string? Many TIA Jason =EXACT(UPPER(D16),D16) -- HTH Bob ...

Function help!!
I am trying to compare 2 worksheets. If a value in worksheet 1 is found in worksheet 2, I want the final value to come over from worksheet 2. for example worksheet 1 a b c d worksheet 2 c 10 d 20 b 30 a 40 would look like worksheet 1 a 40 b 30 c 20 d 10 Please help You want Vlookup, as in: =vlookup(a1,sheet2!a:b,2,false) Regards, Fred "Kathie" <Kathie@discussions.microsoft.com> wrote in message news:E1EE3CAD-3650-4737-841F-DEE7039751A4@microsoft.com... >I am trying ...

Matching user entry to database value and pasting to correct cells
I have a workbook with 2 worksheets. Basically it is a daily sales log. On worksheet 1 the user will input multiple daily sales figures and the date. Worksheet 2 is the historical sales figures. The theoretical usage will be for the end user to go to worksheet 1, enter the date and daily sales figures and click the save data button. I am seeking a formula that will look at the date entry and paste the values from worksheet 1 into the proper line on worksheet 2 based on the date entry from worksheet 1. Example: Column A of worksheet 2 is simply a list of dates. January 13, 2...

Today(S) function in DASL Filter
I am trying to figure out how to use the "undocumented" Today(S) function. Basically I have about 10 search folders that make use of most of these: %yesterday %today %tomorrow %last7days %next7days %lastweek %thisweek %nextweek %lastmonth %thismonth %nextmonth So I am also hoping that these functions also allow for passing of a (S) seconds parameter. The reason I am hoping to do this is to pre-adjust my UTC DateTime field "MileStone1UTC" so that it will be normalized into local date/time. e.g. "http://schemas.microsoft.com/mapi/string/{00...

Exporting function from MFC application
I want to call a function in the main MFC C++ program from a regular dll (its like the opposite direction of exporting from a dll). Is it possible to do so with a global function? Is it possible to do so with a class function? Can someone explain and demonstrate with a simple code? Regards Galia In the DLL Something like Class ClassIndll { Virtuall void aCallBackFunc()=0; }; In The .Exe Class MyClass : public ClassIndll void aCallBackFunc(); }; Implement the aCallBackFunc in the .exe and instansiate MyClass. Pass a MyClass pointer to the .dll which call ->aCallBackFunc(); Re...

How do I change percentage function to less than a hundredth?
Please clarify with an example -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Georgia" <Georgia@discussions.microsoft.com> wrote in message news:7C4F6C1E-3A8F-48BC-B57E-E28CFD3CF543@microsoft.com... > ...

Check Register with original G/L posting
Is there a report out there that would tell me the original G/L coding of the invoices being paid on check runs for a particular date range? Thank you. Todd, There is no such 'canned report', but if you're looking to create your own, you could accomplish this by combining the code from 2 views I have published: Payables Apply Information: http://victoriayudin.com/2008/10/22/sql-view-ap-apply-in-gp/ Payables GL Distributions: http://victoriayudin.com/2008/11/28/sql-view-to-show-all-gl-distributions-for-ap-transactions/ -- Victoria Yudin Dynamics GP MVP Flexible Solutions ...

DATA VALIDATION with LEFT function
I would like to use a data validation – List and enter only the first character from that list. For example my list is below and named “Types” C - Construction D – Demolition E – Equipment Installation R – Repair The cell to be filled only needs the first letter not the whole content of the list. I try to use LEFT function in the Validation Source and I can not make it work with The name source “=Left(=Types,1)” Can someone help or suggest a different way use the data validation, like use two columns display the description in one and enter data from the other. Thanks. Sounds like you...

don't know what function to use
If I have in column Z1-Z10 ten company names, then in columns AA-AJ those same company names with the area's they cover in the respective rows. Is there anyway to have an input area in A1 so that excel that will look at that information and be able to pick a company that covers that area AND be able to know and rotate through the available list in order giving me the answer in B2. so lets say that Jane goes to New York, Los Angelos and Joe goes to Los Angelos and Chicago. I input New York and the out put is Jane. I then input Los Angelos and the out put is Joe since Jan...

Font size on checks in OA Pro 2008
I test printed a few checks in OA Pro 2008 just to check the alignment; what I would like to do is increase font size somewhat so I can use a window envelope to mail. Right now the font size is just too small to make me believe the USPS will attempt to read it. Thanks for any and all suggestions. ...

what function to use?
What function do i use? eg. DATE PAYMENT CODE 01/03/2010 J96 01/03/2010 B24 02/03/2010 C25 04/03/2010 J96 04/03/2010 U96 04/03/2010 J96 im trying to count how many times the letter J in payment code is used in a specific date.... eg how many J codes was used on 04/03/2010? Please help. Hi =SUMPRODUCT((LEFT($B$2:$B$100="J")*($A$2:$A$100=DATE(2010,3,4))) -- Regards Roger Govier TJ wrote: > What function do i use? > > eg....

Countif function
I am trying to count the number of records between dates. I have trie =countif(x:x, month(a2)), where x has date formats dd/mm/yyyy. It i returning 0, and should be returning a value. Thanks in advanc -- Message posted from http://www.ExcelForum.com Hi Try something like: =SUMPRODUCT(--(MONTH(A2:A100)=6)) -- Andy. "Pascale >" <<Pascale.1at9py@excelforum-nospam.com> wrote in message news:Pascale.1at9py@excelforum-nospam.com... > I am trying to count the number of records between dates. I have tried > =countif(x:x, month(a2)), where x has date formats dd/mm/y...

Another question about IF(COUNTIF) checks in Excel
<Tiff1618@discussions.microsoft.com> wrote in message > news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com... > > Hey again, > > > > Is there a formula I can use to figure out if there is one specific phrase > > in a selection? > > > > I'm updating the attendance prgram at the school that I work at. Every > > student has their own attendance sheet in Excel, and each sheet sort of > > looks > > like this: > > > > |Monday| > > Period 1:| A | (A=Absent; S=Seat time) > > Per...

Where to find translation of Excel functions
Hi! I have an annoying problem I wanted to solve long time ago. When I use an Excel version other than English, I keep forgetting the equivalent in the other language (e.g. "sum" is "summa" in the Swedish and "Somme" in French version). I bet there is a smart way to solve this problem. Any ideas? Many thanx! Try http://cherbe.free.fr/traduc_fonctions_xl97.html -- Regards, Peo Sjoblom "Alfred" <lavedure@hotmail.com> wrote in message news:3fba781b_1@news.estpak.ee... > Hi! > > I have an annoying problem I wanted to solve long tim...

synchronizing mouse button events with a thread function
I have an unusual way of using the lock, but I'm not sure if it is a good approach. Suppose I need to synchronize mouse button event with a function from another process, I'm thinking of using the following method: CMutex m_mtx; CSingleLock m_MouseEventLock; OnLButtonDown { m_MouseEventLock = CSingleLock(m_mtx); m_MouseEventLock.Lock(); ... } OnLButtonUp { ... m_MouseEventLock.Unlock(); ... } ThreadProcFunc { ... CSingleLock lock(m_mtx); lock.Lock(); ... lock.Unlock(); } My concern here is reusing the lock variable in the mouse button event. Is it allowed....

check XmlDeclaration
How can I check that a document has XMLDeclaration ? i.e. it has <?xml version="1.0" encoding="utf-8"?> in the beginning thanks, xke "xke" <xkeops@gmail.com> wrote in message news:1193171712.723590.249920@i13g2000prf.googlegroups.com... > How can I check that a document has XMLDeclaration ? > > i.e. it has <?xml version="1.0" encoding="utf-8"?> in the beginning > > thanks, > xke > You can't using standard XML/DOM/XSLT methods. The declaration is only really there to help the (de)serialisation and...

Bills summary
Is there a way to make the billing summary show the "after" amount for more than just one bill at a time? I'd really like to be able to see what my checking account will look like after a certain date without having to do math myself, this "current" and "after" feature would be perfect if it could. Kevin ...

check date if it falls within a month
Hi, I was wondering if anyone might be able to help me here. What i am trying to do here is to be able to tell if the entered date by a user is valid in a sense that it still falls within the range of a month. For example, users might by accident enter in 29/02/2010 when there was no such date in the calendar. I want to be able to write a code to check the date before proceeding any further. The other problem I am having is since the date field is of type date/time, everytime trying to put in 29/02/2010 in the textbox, Access will change it into 2029/02/10 automatically. I c...

batch created by check links process
we have 2 batches that were created by the check links process - but we do not seem to be able to delete them or post them and the batchIDs are also blank (normally a reqd field) they both have 1 trx in each with a zero balance.,., do we run check links again? thanks Theo :) If the Batch IDs are blank, can you then assign a dummy batch id then delete or post it. doesn't matter if you post them, the value is zero anyways. -- http://ddelprado.blogspot.com "Theo" wrote: > we have 2 batches that were created by the check links process - but we do > not seem to be abl...

Data entry
As an exercise, I have a bunch of cells and a command button to perform a calculation on them. The user enters data in the cells and when ready clicks the button. This works as expected. However, if the button is clicked while the active cell is having data entered (i.e the user has typed something but not yet tabbed out of the cell) then the program hangs. Is there a method that can detect that the data entry has not been completed to avoid this situation? Gord. Gord, Are you sure the program hangs? It would seem to me that as input is not complete and the cell is still being edited...

Problem with drop-downs in Outlook 2002 entries for date entry
I recently had to reformat my c: drive and to reinstall WinXP and my applications, including Outlook 2002. Since then, I�ve had a number of problems, most of which have been solved, often with help from people on these forums. One problem has not. When I am entering a date through a drop-down in the Calendar or Tasks, although the month calendar with left and right arrows for moving forward and back appears when I click my mouse on the drop-own arrow, the moment I move the mouse pointer away from the drop-own arrow, the entry calendar disappears. This makes it impossible for me to use that...