In the following formula I'm looking for MONTH 1 (January) and WEEKDAY 1 (Sunday): =SUMPRODUCT(--(MONTH(Sheet1!$A$2:$A$6936)=1),--(WEEKDAY(Sheet1!$A$2:$A$6936)=1),--(Sheet1!$D$2:$D$6936=$D2),Sheet1!E$2:E$6936)

is it possible to find MONTH 1 and 2 (Janauary and February) and use the rest of the formula as is? Can the same thing be done looking for MONTHs 3,4,5 or months 8,9,10,and 11?

I'm graphing data from November 1994 to present. Since it's such a long time period, I only want to indicate a given year once (1994, 1995, 1996, etc.) with a tick mark every quarter (e.g. 1994 | | | 1995 | | | , etc).

I'm having difficulty doing so since the graph starts in November. The graph right now shows 1994 as a full year and thus the data doesn't correspond with the correct dates.

I have about 80,000 dates. What I need to do is to place an x by the date if it falls inbetween January. For example, I have start Date in A1 and End date in B1. If the start date was 10/20/2013 and the end date was 6/11/2014 then that woub give me an "x" because it went through January. If the start date was 11/23/2013 and the end date was 1/1/2014 that would give me an "x" because it fell in January but if I had 8/4/2013 as a start date and 12/29/2013 then that would be blank. Hope that I made myself clear enough.

I'm using a formula (with the SUMPRODUCT function) within which I need to match the month & year from an array with 3 "true" possibilities...

Meaning that if data (month & year) from the array matches any of the 3 months & year of a specific quarter (for example July 08, August 08, September 08), the result of that section should be "True".

The data to be matched (any of the 3 months of each quarter) is drawn from a table header (showing every month of the year) by using this "monthly" formula:

TEXT(H7;"mm.yyyy") where H7 holds "01.07.2008" (but displays "Jul. 08") and returns "07.2008".

I'm trying to extend it to something like:

TEXT({H$7;I$7;J$7};"mm.yyyy") to match either July 2008 (H7), August 2008 (I7) or September 2008 (J7) for the 3rd quarter of 2008 but when I hit Enter, I get an error and H$7 is highlighted ("ctrl + shift + enter" doesn't help).

Here's an excerpt of the formula that works well for other queries on my worksheet:

=SUMPRODUCT(--(GLCodes=$B7),MasterNetAmt_01) I want to dis-include the CC="06" in the summation. I tried using: =SUMPRODUCT(--(GLCodes=$B7),(cc="06"),MasterNetAmt_01)

I have a criteria issue with a sumproduct formula i'm using. The criteria is essentially supposed to say "if the value in column I (in Sheet2) equals ANY value in column O (in Sheet1), then do not include that line in the SUM. Here is the complete formula:

I'm looking at the formula in Sheet3, cell F5. You'll see that the criteria "--(Sheet2!I:I<>Sheet1!O:O)" does not seem to be working. I need the formula to not include any values from Sheet2!E:E, where the corresponding value in column I does not equal any value in Sheet1!O:O. Also, these values can change by user so its not as simple as just typing them in to the formula as an array...

That VBA part of this is that it's in a macro, so if there is any way to make this easier using code instead of a simple formula.

I'm using Excel 2007 and have an Employee Scheduling Program that keeps track of 10 employees on a monthly basis (1 worksheet per month). The days of each month are in columns (I thru AM) and my 10 employees are in Rows 6 thru 15, which creates a grid of cells. I use Conditional Formatting to highlight the Weekends, Todays Date, and Holidays. My Sumproduct formula (shown below) is in each of the cells of my grid and places a number (1 to 10 for each employee) from start date to the end date. My Current formula works great as it finds every occurrence of the argument but I need to modify it to include the contents of the Helper Column.

I have no problems figuring out SUMIF or SUMPRODUCT with multiple criteria, but I'm trying to help someone with an issue that the second criteria is actually not all-inclusive.

Column B - Lots of numbers, the SUM range Column C - Yes and No flags. Y or N

C1 = maximum number of matching items to include.

So, =SUMPRODUCT((C2:C100="Y")*(B2:B100)) sums up ALL the Yes rows, but the guy wants to restrict it to the last X matches, and put that X factor in C1. So if C1=5, only the last 5 Y matches are included.

I am trying to update a spreadsheet for 2012 to 2013 and want to use a formula to change SUNDAY 8 January to Sunday 9 January and so on for rest of year.

I have a a set of date ranges. The start and stop date of the ranges are listed in seperate columns. I need to count how many of those date ranges include a specific month/year. Example data is below.

I am having a ton of trouble with this. I am trying to add a single text row in between existing data. I have tried multiple loops to try to place an array into Column G, but I cannot figure it out. Is there some coding that will produce the sort of result below in column G?

I m trying to use arrays in the wrong way. I simply want to create an array from values in a column and perform calculations on each element and store the results for each element. I therefor need a 2d array. I also dont know the number of values in the column and therefor dont know the number of elements that will be in the array. How would I declare this array. Ive tried Dim MyArr(1 To ActiveSheet.UsedRange.Rows.Count, 1 To 2), but that returns an error - 'Constant expression required'.

Basically, I'm trying to alter the code by inserting another condition where if S12 = R than it will exclude the value found in D12, other wise D12 is included. It was initally setup with a range of $C12:$H12 but i had to write it out long to exclude $D12

A few weeks back you guys were kind enough to help me figure out a good way to use SUMPRODUCT to fetch a value from a third column using values from, in my case, two other columns. It has been a lifesaver! Now that my database grows to more than one sheet however, I am again stumped finding a method of using SUMPRODUCT to search the same data base on the next page of the workbook. Everything is the same except value of the numbers so if I can figure out how to make SUMPRODUCT drop through the first empty cell or something similar, I can continue my database search without more sleepless nights.

The values are in few enough columns such that one could put them all on one sheet, however I don`t know how to make Excel start at the top of a particular column once it has been unsuccessful in the first attempt to find the value.

If have very simple =IF function, =IF(F4=H4;1;0) but don't wan't the 'value is true' value when both cells are empty, how do I need to change the function so it states 0?

But I get a N/A , my reference A3 matches the dates in the database and C2 matches the repeated occurances of Remnant...

Is this possible since my templates keep repeating down my database?

Basically, each template in the database is related to different stations, and I am trying to sum all the stations details in the database for each month and Header"Remant"

I am working in a spreadsheet that contains a bunch of data, it is not limited, and varies. It has some fiels such as: name, date of birth, address, and others. I am interested in creating a column with only the month digit of the date of birth for each row.

I have been working with some code, I am not quite sure how to continue. The following table would be a example that I have of the data, it has only to entry in the column of Date of Birth, but my data range will always vary. I want to get the month and past it in the next cell that is available in this case would be column c or 3... The worksheet name is REP.

VB:

Dim Cell As Range ' 1st cell with the posting date Set Cell = Range("A2")

I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.

I have never worked on workdays before so I do not know most of the available formula and play around. I googled across and found this one: [URL] .......

Where Bob has given an ARRAY formula.

Following is my layout in A1 to C3 grid (for testing purpose): Start Date11/1/2011HOLIDAYEnd Date11/30/201111/3/2011NETWORKDAYS2011/22/2011 The formula resides in B3.

Is there any other development in 2007+ versions of Excel which can handle 6 working days in a week?

I have 3 or 4 criteria and one data range to sum based on these criteria. If I remove date as a criteria, I can make a sumproduct formula work, and even if I include a specific month it works as well, I would like to write a formula that uses the most recent month with data as a criteria. My reasons are that some banks pay our reps monthly, some quarterly, some semi-annually, and some whenever they damn feel like it. I need to take a snapshot of what each rep has invested in each bank: So for Bank A his last Statement came in March, but Bank B sent a statement in January. Here's what I have so far:

I know that the Month function here is incorrect but I'm going bonkers trying to wrap my head around this.

FYI, 2007 Data is my Data page, Col F = Date Range, Col G = the Rep ID, Col B, = the Bank name, Col H = Assets Invested; Sheet1 = my results page.

I know, I KNOW, many of you will scratch your heads and say this idiot should just use a Pivot table, I know. I have. Let's just say some of the end-users are not comfortable with Pivot Tables. I've come up with a simple listbox/command button to populate the axes of the chart (Column A = Reps, Row 1 = Banks)and I need this formula inside the chart.

After seeing and learning from many of the posts on this site, I'm sure someone knows how to acheive what I'm after, I think it's just a matter of my communicating it as well as I can.

I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.

There is a date field and three "attempt" fields. If The first attempt was a fail and the second a pass this must count as a pass and not a fail. If the first attempt was a fail and second was a fail then this must count as a fail, same again for third column.

On the totals tab there should be a total against each date for the fails. As you can see iv tried doing by sumproduct but its not working, i understand why its not working but i just can't see how I can get it right.

I am trying to count dates from a list using sum product (I found the formula via google) I have plugged it into my spreadsheet but it does not seem to be calculating correctly.

I have attached the spreadsheet too : sumproduct_Error.xlsx