# Formula - Week Product Level Count

Jan 5, 2014How to solve my problem in attached file : Week Product Level Count.xlsx

View 1 RepliesHow to solve my problem in attached file : Week Product Level Count.xlsx

View 1 RepliesADVERTISEMENT

Is there a simpler way to to the calculations on the spreedsheet i have attached?

Basically I want to count up the components used to make one product

I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:

Column BA list of dates

Column C & DNumerical data not used in any calculations

Column EThe numbers to multiply together

Cell A1Start date to be used in the PRODUCTformula

Cell A2End date to be used in the PRODUCT formula

The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:

{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}

This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.

I need to be able to keep a running count of how many gallons of gas i put in my car each week. Each week is one column. Column A is where i want the total to show.

column B,C,D,E...etc is where i put the numbers in this is all in row 1 for now.

currently i have =sum(B1:BB1). But something is not right because it is not adding the numbers together it will only add what is already there not any numbers that i put in after the formula is made. Do i have the wrong formula or something else wrong. My goal is to see how many gallons i put in at the end of the year, month, quarter, and so i have several other reason for this info.

I have 2 columns in my spreadsheet:

B:B is a column of dates.

C:C is a list of names

formula that will count the number of times the name 'SIMON' appears in column C:C but here is the catch: I only want to know how many times that name has appeared over the course of the previous week. IE NOW - 7days

I have a spreadsheet that is being used for tracking work completed each day in a week. Each day has 5 columns and 10 rows of data to potentially be entered, some of the data is text and some is numerical. Can anyone help me come up with a formula that will count the text and the numerical entries for each day? I tried to use a nested if statement but it exceeded the number of nestings available. The range of cells for the first group is B6:F16.

As a side question, will count work for a range of data or only one column or row at a time?

I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.

What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.

I have a very big Excel table with many products. A product like a "pen" can have different attributes, then each of it gets an own row. Each row has one entry in the column "country". It indicates in which country the product gets shipped.

I want to count the country, in which the products get shipped.

But I don't want to count two times if a product "pen - blue" and "pen - yellow" both get shipped to the US.

I tried it to achieve this with a pivot table but it counts every row and I haven't found a way that it only counts each country per product once.

I have an simpliefied example attached. The columns "product" and "country" exist already, the column "country count" I hope to get automatically.!

Country

Product

Country count

Attribute

US

Pen

2

Blue

[Code] .......

I have been using the sumproduct formula to count how many times a certain piece of data appears...which works fine...for text purposes such as names, initials etc...

I now need to amend the formula to count how many times a date appears in a range of dates.

I know when you use the sumproduct...dates must be input and formatted as text.

example

A1

01-Jan

02-Jan

5-Feb

7-Feb

12-Feb

formula to only count dates with feb in?

I need to identify every 5th occurence of a product code (In column B)

the list won't be consistent i.e. Code 80100811 may appear in row 2, 17, 35, 47, 51

So I would need to identify the one in Row 51 in the above example,

WorksOrderNumber

Product Code

DateEntered

Column1

56

80100811

01/08/2012 00:00

[code]......

I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.

I'm using weekday formula but no luck.

I'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to.

So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet.

The drop down list displays the products and can be added to by typing new product names on the other sheet.

What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.

I am making a chart for displaying the amount in inches that we have in the water tanks.

The idea is to have a setup like this...

I'm building a sales report that has various formulas to work out rebate percentages based on the number of units.

I've written the if statements for these but how to get formula to work out the number of units required to achieve the next highest rebate level.

rebate levels

>=150 units= 1% rebate

>=300 units =2% rebate

>=600 units =3% rebate

column I has the unit totals and in column M I need to have a formula to work out how many units the customer would need to purchase to achieve the next rebate level. Headers are row 1 and data begins in row 2.

I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.

View 4 Replies View RelatedI have this formula that opens the current folder where my file is stored:

=HYPERLINK(MID(CELL("filename"),1,FIND("[",CELL("filename"))-1),"Open Current Folder")

This works fine. I'd like it to open the folder one level up from the current folder, but I really don't understand formulas well enough to know if this is even possible.

I have a spreadsheet that I am using for capacity planning. We want to automatically figure out which months have 4 weeks and which months have 5 weeks.

The weeks in a month are defined by work week (Mon to Fri), and if the work week has 3 or more days, it is considered a week of that month, if it has two or less days, it is not counted as a week during that month (it will be counted part of the following month).

ie: April 2014 would be a 5 week month, May 2014 would be a 4 week month and June would be a 4 week month.

Apr-14

May-14

Jun-14

Jul-14

Aug-14

Sep-14

Oct-14

Nov-14

Dec-14

Jan-15

Feb-15

Mar-15

[code].....

I have a Sumproduct formula that should be resulting in 0. However, it is resulting in a very small number. I have 2 columns of data. The first one contains -169.5, -123.4, -34, -67.4, 169.5, 123.4, 34, and 67.4. The second column contains all 1's. When I create the following formula, =SUMPRODUCT(H27:H35,J27:J35), it results in 5.68434E-14.

Sometimes, when I have different numbers in the first column, but it still adds up to 0, the Sumproduct works.

What I need to do is count the number of cases and values of everything written between the first of January and today (242).

I know how to do it by counting the days from the 1st of Jan to today but how can I do it so it automatically updates each day.

This is what I have at the moment.

The product name is in K, the date is in H and the value is in M. The range is from cell 7 to cell 94

=SUMPRODUCT(--($K$7:$K$94="Pension"),--($H$7:$H$94<=TODAY()),--($H$7:$H$94>=TODAY()-242),$M$7:$M$94)

I have data that is added to every week. I need an equation to count how many times each employee show up each weekending.

View 3 Replies View RelatedWorksheet is an action log. It has a column containing "due dates". I want to count all rows (dates) that are "less than" today (to see what work is overdue). I also want to count all rows (dates) that will come due in a week. Conditional formatting highlights them OK but I also want to maintain a set of counts. I can do all this in a macro but I don't want to use macros, just formula.

View 2 Replies View RelatedI am trying to produce a excel spreadsheet on Excel 2003 that has sales data. At the end I am to produce where my supervisor can produce a summary of the data showing the last weeks work (and only the last weeks) and how many yes rows there were and how many no rows there were for that week. There is a lot more data involved but the only relevant data for the problem I have is the date and Yes/No fields.

I have looked on other sites and the only thing that I can find that is close is the following formulas.

=COUNTIF(Sheet1!A:A,">"&E1-7)-COUNTIF(Sheet1!A:A,">"&E1)

=SUMIF(Sheet1!A:A,">"&E1-7,Sheet1!C:C)-SUMIF(Sheet1!A:A,">"&E1,Sheet1!C:C)

Apparently these will count the rows and sum them. However they don't differentuate between yes and no. I have attached a very basic spreadsheet with the problem.

I have a worksheet that lasts for a year that is something look like this:

Date Name

1/1 John

2/1 Erwin

3/1 Robert

4/1 John

16/1 Erwin

17/1 Erwin

17/1 John

In my report worksheet, I need a formula to count everyone's attendance from their participation dates based on week

Name Week1 Week3

John 2 1

Erwin 1 2

Robert 1 0

I am trying to count the number of events by week. My column is title weekof and of it I have made a defined range...weekof. My formula is simple countif(weekof, "2/9/14")

The formula providing the 2/9/14 is =$E2-WEEKDAY($E2)+1

Countif seems not to recognize the date at all. I found a posting showing this to work =COUNTIF(Weekof,"

The following formula takes a census of people in our area by date and by hour (sample data attached): =SUMPRODUCT(--(FLOOR(Data!$B$4:$B$39,1/24)<=$B2+C$1)*(Data!$C$4:$C$39>=$B2+C$1))

The formula works great, but I would like to add another component whereby the census will only count someone if they have a certain description (see column A - with color descriptions). So how can I setup the formula, to say, only look at the "red" colors during the same specified dates/times and give me the same type of census...instead of looking at all of the data or creating 50 different ranges for all the colors?

I'm trying to do what I think its a simple formula with Excel 03 but can't get it to work. The info is in two work sheets - A&B.

[code]WORKSHEET A WORKSHEET B

A COLUMN 1 COLUMN 2 COLUMN 3 COLUMN1 COLUMN 2

B BLANK BLANK 10 BLANK DATE:1/28/09

C NAME DATE WORKED 12 NAME

D Jones 1/2/09 13 Bell

E Smith 1/6/09 12 Smith

F Cox 1/28/09 12 Cox

G Cox 1/28/09 10 Collins

H Collins 1/28/09 11 Jones

In worksheet B, Column 2, I need to pull the SUM of COLUMN 3 IF THE NAMES IN BOTH MATCH AND THE DATES MATCH

I've spent hours tyring to get this thing done!

I am stumped on coming up with a dynamic formula that calculates a value based on an increasing number of "sums of products". Specifically, I need to calculate the total number of repeat users based on a "decaying' repeat rate. In other words, I need it to calculate = Sum[... +(d5*f5)+(d4*g4)+(d3*h3)]

or in equation form:

where

n = (1, "infinity")

and

i = {1, 2, ... (n-1)}

with

"Xn" is the calculated value of repeat users for month "n" (output/result cell)

"mi" is the number of new users (in month i)

"Nn-i" is the column 'number' of the repeat rate value (%) multiplier

Here is a screenshot of the first couple rows to illustrate:

I actually have about 70 months of data (rows) and roughly the same amount of columns representing a decaying repeat rate (70 or so) and these will be increasing as time goes on so I need this formula to be dynamic (i.e. - copy it down the column of (# of repeat users)

I am using Excel for Mac 2011.

I currently have a formula that totals a product between two dates, however I need to add up a reason code for that product also in a different tab. My current formula is

=SUMPRODUCT(--('Product'!$A$2:$A$1069=B6), --('Product'!$B$2:$B$1069=Data!$A$1))

I want to add another section so that it looks at the dates between C6 & B6 and also the product in Data A1 but totals the reason from cell C10 in the data worksheet.

I want to find the number of times the 23rd happens between a range of dates.

Let's say I invoice a customer on 5/23/12 (Column C) and I've sent an invoice every month on the 23rd. I want to count how many invoices I've sent during the past few months. I need a formula which tells me how many times the 23rd of the month happens between 5/1/12 (in column A) and 12/15/12 (in column B).

I thought it might be =COUNTIFS(A1:B1,DAY(C1)) but that formula is for counting cells in a range.

I found formulas for counting the specific day of the week (like Tuesday) and for counting the number of cells containing a number, but not this.

Currently having problems getting correct head count. I have formula that works for rows 6-8 but fails in row9. The should be answers are in rows 17-20.

Conditions used in formula

*Start date > Start FY =0

* Current Week > End Date =0

* End Date < Start FY =0

The date difference is divided by 7 because there are 7 working days in a week. If it is greater then 7 then it would be 1 for current week. I tried zipping the file but I could not shrink it to required size. find on weblink below: http://maxupload.com/E759C9D9

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