• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct help!

P

prazad82

Member
Please help me find a solution.


Product range: k5:k99

Product Type: d5:d99

Dates processed: C5:C99


I want to find the number of products and types processed between particular dates. thanks
 
Hi ,


You can use the following formulae , both entered as array formulae , using CTRL SHIFT ENTER :


1. Number of products :

[pre]
Code: 
=SUM(IF(($C$5:$C$99>=$A$1)*($C$5:$C$99<=$B$1),1/(COUNTIFS($C$5:$C$99,">="&$A$1,$C$5:$C$99,"<="&$B$1,$K$5:$K$99,$K$5:$K$99)),0))
2.  Number of product types :

=SUM(IF(($C$5:$C$99>=$A$1)*($C$5:$C$99<=$B$1),1/(COUNTIFS($C$5:$C$99,">="&$A$1,$C$5:$C$99,"<="&$B$1,$D$5:$D$99,$D$5:$D$99)),0))
[/pre]
I have assumed that the two dates between which you want the results , are in A1 ( start date ) and B1 ( end date ).


Narayan
 
Hey, thank you for your quick reply.


But the formula you suggested did not work. May be I was not clear in my question.


I have the data as below


DATA

Date Type Product

Wk1 2-Apr External T1

2-Apr External T3

2-Apr External T6

2-Apr External T4

2-Apr External L1

2-Apr External L5

2-Apr External K9

3-Apr Internal H2

3-Apr External R5

3-Apr External R1

3-Apr External SS

3-Apr External T1

3-Apr External T2

3-Apr External R4

3-Apr External A8

4-Apr Internal C2

4-Apr Internal A7

4-Apr Internal S2

4-Apr Internal S7

4-Apr External A9

4-Apr External N3

4-Apr External O9

4-Apr External M2

5-Apr Internal R4

5-Apr External S6

5-Apr External V8

5-Apr External M2

5-Apr External Z1

5-Apr External K8


Now, I need to get the completion status updated as per the audit plan. I have target audits for each of the products. So I need to maintain a completion tracker. This is what I need to get.


For wk1


External audit = No of audits for a product/week

Internal audit = No of audits for a product/week


For Wk2


Same as above, but the result should not overlap.


I hope this is not confusing, or there is an option to upload the file, please let me know.
 
Hi ,


Sorry , but I cannot understand your data or your requirement ; please upload your file. Check out this link for the way in which you can do so.


http://chandoo.org/forums/topic/posting-a-sample-workbook


Narayan
 
Thanks for the info. Here is the link:


http://www.4shared.com/file/lkC9Q9Uf/audit_data.html


I hope to hear from you soon!
 
The above sheet has only one week's data. How do we calculate sumproduct of "product" against "type" for a range of "dates"?
 
Prazad82


Firstly, Welcome to the Chandoo.org Forums


Can you please upload your file to a location that doesn't require registration
 
This works but dont enter it as an array. just use normal enter


=SUMPRODUCT(1*($E$4:$E$32=H4)*($D$4:$D$32=$K$3))
 
Thanks Montrey. I have a question here. Lets consider the same tracker.


Conditions:


1. The "audit data" part is blank (let say, I am preparing the tracker for a forthcoming month)

2. we enter the formula that provided above in the "Completion" part (so that while entering data in the "audit Data" it will calculate automatically).


Questions:


As we know that we have separate columns for "Completion" for every week. So the data range for a week is unsure (The number of audits can be more "or" less, as we have not started filling the "Audit Data" part). How do we lock the formula saying that it should only calculate the data within a range?


Thanks
 
Well you would make a dynamic range. You would name each range and in the name editor you would use an offset formula.


Is this what you are asking?
 
Montrey, yes it is a dynamic range. I think naming the range is a great idea, but how do we do this? Since the range size can vary (sometimes, the range contains 30 audits/week, sometimes 50 audits/week), i am not sure. please let me know. I hope you got my question.
 
Here ya go. Just change the column accordingly.


=OFFSET(Sheet1!$E$4,,,COUNTA(Sheet1!$E:$E)-1)


Create a named range with that formula then put this named range in your sumproduct formula. Remember to make a named range for each column of data then put them into your formula accordingly
 
How do i do a sumproduct between a prefixed dates?


Beginnig date: 6 May

End Date: 12 May


This is my formula


=SUMPRODUCT((Tracker!$K$5:$K$241=Plan!$C5)*(Tracker!$D$5:$D$241=Plan!$N$4))


How do I incorporate the start and end dates?


Please help
 
I think a PivotTable would actually be the fastest way to do all of this, but if you really want a formula, think along these lines:

=SUMPRODUCT((ProductRange=Product)*(ProductTypeRange=ProductType)*(DateRange>=BeginningDate)*(DateRange<=EndDate))
 
Hi, prazad82!


This topic is the same as last question of this, just a few minutes of difference:

http://chandoo.org/forums/topic/sumproduct-incorporating-date


What you're doing "is called cross-posting and is generally frowned upon in the Blogosphere as it causes people to potentially waste our time when a question is already answered. You should also check and respond to posts and let posters know if they are heading in the write direction or not." Hui's dixit, SIC. And I agree 101%.


If you'd have read the first three green sticky posts at this forums main page...

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

...you should have noticed this points:


"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."


"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."


"Cross Posting, generally it is considered poor practice to cross post, that is to post the same question on several forums in the hope of getting a response quicker."


"If you do cross post, please put that in your post."


"Also if you have cross posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting there time on your answered question."


"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."


"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."


Regards!
 
@Montrey

Hi!

I'd rather see it as an educational, pedagogical, polite, diplomatic, instructive, elliptical, ... shall I go on or it's enough?... way to inform new people that haven't read the general guidelines. Just aiding newcomers to get imbibed with the spirit of these forums.

Regards!

PS: I use to show a slightly most direct face in many others posts, but not as a welcome. Surely you've read me... once indeed today, earlier.
 
You must log in or register to reply here.
Back
Top