SUMPRODUCT Miscounting Dates

Aug 15, 2013

I have a problem with the formulae SUMPRODUCT, it seems to be miscounting? Now I am sure there is something silly I have missed, but I could really do woth someone else taking a glance over it before I have to look at another option to get the result I need.

The ultimate goal here is to count how many occurrences happen in each month, there is a list in Sheet1 of the attached Excel document, then in Sheet2 there is the two different formulas I have tried, but the count is wrong? For teh first month of instances June 2012 it counts 20, but there are 21?

Example of Issue.xlsx

View 8 Replies


ADVERTISEMENT

Sumproduct Between Two Dates

Oct 14, 2012

I am required to work out some transaction vlaues, that fall between two dates.

I thought since i have 4 arguments to attend to, sumproduct would do the trick. It wont work and i cant work out why

The base of my argument is to look to locate if a particluar account has had transactions processed between a specfied date range, if so sum the period.

Date 1
date2
Account Number
Volume
Value

16/04/12
01/05/12
14142541
0
ÂŁ0.00

[code]....

View 9 Replies View Related

Sumproduct Formula With Dates

Apr 12, 2007

I have a workbook which contains multiple lines of data. In column H it contains a date. I want to count how many of these dates occur in January. I also want to be able to add other criteria as well. Example, column H = Janaury and column D=Open. In cell B2 I have the number 1 which represents “January”. Below is my formula but it returns an error.

=SUMPRODUCT((MONTH('Raw Data'!H:H)=B2))

View 9 Replies View Related

SUMPRODUCT In VBA With Dates As Strings

Nov 4, 2007

I am currently coding up a VBA macro that will counts up the number of occurrences of a particular event for a particular date. The problem is that it keeps returning zero as the result. I thought it might have been a data type mismatch issue as the cell format is a date in the mm/dd/yyyy format but the data is stored as a string so that I can easily manipulate it with the built in VBA functions. I tried changing the data type but still received zero as the result. 'a' and 'c' refer to cell ranges, criteria is a string of characters and criteria2 is a string that pulls in a date in the format of mm/dd/yyyy. I know this code works when I don't use dates but I can't figure out why it doesn't work with dates.


criteria = Application.Workbooks(FileName).Worksheets("Day_Counts").Range("B1").offset(0, column).Value

View 9 Replies View Related

Sumproduct Not Calculating The Correct Dates

Aug 17, 2009

I have put together the following code (with help from this and another forum):

View 5 Replies View Related

Using Sumproduct To Sum Networkdays Between Dates With Criteria?

Oct 15, 2008

Hmmmm. Looks like I ruined my original posting while trying to mark it solved.

View 14 Replies View Related

Add Sales Between Two Dates Without Using Sum Or Sumproduct Function

Jun 23, 2012

Query below

Date
Sales

1/7/2011
500

2/7/2011
600

[Code] ........

I was given the above table in an interview and was asked to find the sum between two dates without using sum and sumproduct.

View 3 Replies View Related

Attempting To Use Countifs / Sumproduct Where The Data Contains Dates

Jun 13, 2014

I"m attempting to generate a report to show the number of occurrences for a particular product within a calendar month.

Data is stored on the first sheet, output is populated on a separate sheet. Seems simple enough, but getting the "month" out of the date, whilst referring to another sheet seems to be the sticking point.

2014/03/24 14:34:17
Product1
2014/03/24 13:45:51
Product2

[Code]....

View 7 Replies View Related

Sumproduct Month And Year - Count Dates From A List

May 11, 2013

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

=SUMPRODUCT(YEAR(Tbl_finish[Finish]=2008)*(MONTH(Tbl_finish[Finish]=1)))

I am expecting a count of all the dates in January 2008 with the formula above.

View 5 Replies View Related

Complex COUNTIFS / SUMPRODUCT Validating Dates Using 2 Ranges

Oct 4, 2013

I am trying to find out how many projects might be active as of a certain date.

I have a tab in excel that contains project data. For each project there is a "Start Date" and there might be an "End Date"

My question is, how do I count all the rows where the start date is Less than a given date and the end date is after a given date? The wrinkle is... if the end date is empty then obviously it should be counted as well (assuming the start date is still before the given date).

Here are the approaches I've used thus far:

=SUMPRODUCT(1*( AND(OR(RawMetrics!P:P > A2, RawMetrics!P:P = ""), RawMetrics!$O:$O < A2 )))
=COUNTIFS(RawMetrics!P:P,">"&Sheet1!A2,RawMetrics!O:O,"

View 4 Replies View Related

Sumproduct- Counts The Correct Amount But Not With The Correct Dates

Jul 3, 2008

I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.

I need to see how many items processed for each set per day.

Example:
[url]

The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))

View 14 Replies View Related

ISDATE >> Sumproduct "Not Dates" In Range

Sep 27, 2008

I have got a cell (A1) with a Date: 13/10/2008

I want a formula like IsDate(A1) = True
without UDF's

My purpose is to obtain a "thing like" : =SUMPRODUCT(Not(Isdate("MyRange"))*"MyRange")

View 9 Replies View Related

Sumproduct -in Column A I Have Dates And In Column B I Have Names

Jan 30, 2008

in column a I have dates and in column b I have names.

eg

A1 = 1/1/08
A2 = 2/3/08
A3 = 3/1/08
A4 = 3/1/08

B1 = Jenny
B2 = Jenny
B3 = Jenny
B4 = Pat

I am trying to count the number of instances of "Jenny" in January.

I tried =sumproduct(A:A,>=39448

View 9 Replies View Related

Grouping Dates In Pivot Data By Month As Well As Sorting Dates In Ascending Order

Apr 30, 2013

I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.

Pivot Table  ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26

View 3 Replies View Related

Auto Fill Dates: The Sheet To List The Dates In A Column For Each Month Automatically

Jun 17, 2007

I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.

View 6 Replies View Related

Excel 2010 :: Dates As Headers In Table Not Recognized As Dates For Charting?

Nov 8, 2012

I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.

If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.

I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.

View 1 Replies View Related

Difficulties With Dates (convert The Column Of Dates To Say Mar 14 2009 Type Date)

May 3, 2009

look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.

View 5 Replies View Related

Excel 2007 :: Unable To Convert Text Dates To System Dates

Oct 21, 2011

I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.

Method 1 : Using Text to Column Wizard (Excel 2007)

I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.

Then I press Control ~ on this data as shown in second view.

Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011

control ~ (overview)

Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786

2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.

Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261

2nd view - system dates are getting converted into 1911

Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911

Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).

View 4 Replies View Related

Pick Range Of Dates And Find Number Of Days Without Sales Between Those Dates

Feb 23, 2014

Here is my set up:

A2 to BF2 is a range of dates

A3 to BF3 are sales. Days without sales are 0.00

I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.

View 3 Replies View Related

Conditional Format Dates In A Calender When Matches Dates In A List

May 14, 2009

Hi Guys, This has been bugging me for a bit now and I just can't sus it...

I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.

I have beside that a column for holidays, etc and then a another column for other events.

When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).

The formula I have been playing with (no success) is:

=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.

The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...

Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...

I have attached the spread sheet that I am working on.

View 2 Replies View Related

Dates Pivot Table US Format But Source Dates European

Apr 4, 2008

there are a multitude of issues with US date formats when you're not in the US but I've run across one that I can't figure out.

I have a source table that has approx 5000 lines on it, everything looks correct and all the dates are in the correct (Australian) format. However when I use it to make a pivot table, any dates that are before the 12/m/yyyy gets changed to the mmddyyyy format in the pivot table only, all the source dates are still correct.

View 3 Replies View Related

Concatenate With Text And Dates Where Some Dates Cells Are Blank?

Nov 14, 2013

I would typically consider myself a decent Excel user, but I haven't been able to solve this one. Maybe it's just the lack of sleep now. I am attempting to combine 4 cells into one where the last cell contains a date. Below is the formula I am using:

=B3&" "&C3&" "&D3&" "&TEXT(E3,"m/d/yy")

It works when all of the cells have values, but the only problem I am having is that some of the dates (in column E) are blank. When this cell is blank the default date that displays is "1/0/00." If I add the typical, " " at the end it says the formula has errors. The need for the TEXT(E3,"m/d/yy") for the cells that contain dates is throwing me off.

View 3 Replies View Related

Formula To Find Missing Dates From A List Of Dates

Jan 22, 2014

I have a tracking template with a column listing dates, all i want to do is find all the missing dates from that column of dates.

Example:

Column A
1-May
2-May
4-May
5-May
7-May
8-May
10-May
11-May
12-May
14-May
15-May

I want to list the missing dates from this list.

View 4 Replies View Related

Convert List Of Dates To Count No. Of Dates By Week

Jan 27, 2006

Basically I have 2 columns each with a list of dates in no particular order (and containing blank cells too), one planned date column and one actual date column.

What I need to do is plot this on a graph, and since the number of dates has no set limit and I dont want to have to plot maybe 100 dates on the x axis, so i want to group them by week before plotting them, i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th jan etc

I have a pivot table that counts how many of each date occurs, i.e. 10 x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.

im sure theres an easyish way of doing it so i can get the 2 lines on the graph for no. of planned dates each week and no. of actual dates each week, i just cant see it.

View 13 Replies View Related

Count Working Days Between 2 Dates, But Return A Zero If The 2 Dates Are The Same Day

Jan 15, 2009

Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date

But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero

Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!

View 7 Replies View Related

Adding Values Between Two Dates And Dates Are Also Derived By Formula?

Dec 9, 2013

I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.

The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column

These dates also have formula by which there are derived

-------------------------Current Ageing-------------------------
Date Range

Bucket
Amount
Percent
Start Date
End Date[code].....

I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.

View 9 Replies View Related

Counting Dates In A Range :: Count Weekend Dates

Feb 4, 2010

In Cells B2:B100, i have dates that which have been entered using a combo box (the dates type is for e.g. 14th March 2010 format)

I want a formula that will count the cells that have dates between 1st April 2010 to 30th June 2010 in cells B2:B100

Also, I would like a formula that counts weekend dates between 1st April 2010 to 30th June 2010?

View 9 Replies View Related

Convert Numbers, Representing Dates To Actual Dates

Oct 15, 2009

I get a spreadsheet every day that has a column of numbers that contains dates that are not in a date format. Is there a way to convert these numbers to a date format? The numbers come from an outside source so I can't modify on the front end. look at the attached spreadsheet with representative data of what I'm trying to ccomplish.

View 2 Replies View Related

Huge Table Of Dates, Find Range Of Dates

Aug 16, 2006

I have a masive table of dates (the date is created via a if formula)
what i need is so wheni enter 2 dates in 2 cells the system checks all the dates between the two specified and then returns the contents of them to a small area on the page.

Dates To Test 14/08/2026 19/08/2026

Results

14/08/2026

15/08/2026

16/08/2026

17/08/2026

18/08/2026

19/08/2026

Table..............

i dont mind using script or anything like that, i dont have much knowlage of it but do have coniderable understanding of other langages and usually figure it out :D

View 3 Replies View Related

Add To Dates Based On Criteria & Flag Overdue Dates

Dec 31, 2006

I have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved