• 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.

Counting widgets made in a given time range

P

Peter Fitzgerald

New Member
I want to count the number of widgets manufactured in a given month.
The data grows to cover the entire year, I'm asking Excel to, for example, search in the 'Date Made' column, look for all dates between July 1 and July 31. The add up all the widgets I made in that time period and give me a daily average. (See uploaded file example)

I will run this monthly.
The number of days on which I manufacture in any month is variable.
There could be a 'Number made' gap if the number of widgets is not yet available or text if I need to re make them.

I have looked at countif, sumif sumifs, IF, and can create a new column to delete out text or zero's, but can't get the date range search to work.

I would appreciate any guidance.

Thank you.
 

Attachments

  • counting widgets.xlsx
    9.3 KB · Views: 11
Welcome to Chandoo.org forums.

CTRL+SHIFT+ENTER following formula and see if it works for you:
=SUM((MONTH(G7:G14)=7)*IF(ISNUMBER(H7:H14),H7:H14,0))
 
Good day Peter

If you turn your data in to a table you can very quickly search on dates and exclude and cells not required such as empty, zero, rejects.

Have a look at the attachment
 

Attachments

  • Peter Fitzgerald.xlsx
    10.7 KB · Views: 2
Peter Fitzgerald said:
I want to count the number of widgets manufactured in a given month.
The data grows to cover the entire year, I'm asking Excel to, for example, search in the 'Date Made' column, look for all dates between July 1 and July 31. The add up all the widgets I made in that time period and give me a daily average. (See uploaded file example)

I will run this monthly.
The number of days on which I manufacture in any month is variable.
There could be a 'Number made' gap if the number of widgets is not yet available or text if I need to re make them.

I have looked at countif, sumif sumifs, IF, and can create a new column to delete out text or zero's, but can't get the date range search to work.

I would appreciate any guidance.

Thank you.
Click to expand...
 
Peter, I'm sure the developers trying to help you read that already. If there's something more you want to clarify, please do so. Not sure if copying the same post over adds any value.
 
Thank you so much for the quick response. I see what you are doing, you are looking for the month 7, and if present, sum the positive value in the corresponding column, otherwise it's value is zero.Correct?
The formula gave a #value error with the message that 'A value in the formula is in the incorrect date format'. Would that refer to the "7" in the formula? I just learnt of a new wrinkle - the table spans multiple years, so it would find July 2012 equally well.
I thought to enter a start and end date in two assigned cells and then have the formula reference those cells. That may make it easier. Thank you for the help - I'm learning!
 
bobhc said:
Good day Peter

If you turn your data in to a table you can very quickly search on dates and exclude and cells not required such as empty, zero, rejects.

Have a look at the attachment
Click to expand...
Thank you for the example. I agree entirely with your remarks. However, this will be used by a team of people who are not so savvy to create a table on the fly. The sheet (18 of them) from which I am working has much more linked information and would get scrambled sooner or later. Thank you fro the idea.
 
By the way, my formula above could be made a little more shorter (assuming you're using an English version of Office)

=SUMPRODUCT((TEXT(G7:G14,"MYYYY")="72013")*N(INDIRECT("R"&ROW(H7:H14)&"C"&COLUMN(H7:H14),)))

Else, instead of MYYYY, use the appropriate alphabets for non-English
 
Or in SUMPRODUCT, if we use 2nd array with "comma" instaed "*", will avoid ALL TEXT values.

=SUMPRODUCT((TEXT(G7:G14,"myyyy")="72013")+0,H7:H14)
 
Haseeb A said:
Hello Peter,

Enter Start & End Date in two cells. ie: A1 & A2

A1: 7/1/2013
A2: 7/31/2013

Then use SUMIF

=SUMIF(G:G,">="&A1,H:H)-SUMIF(G:G,">"&A2,H:H)
Click to expand...
This worked too, and would be easy to implement in the environment in which the spreasdsheet is used. Useful point about the Comma rather than "*". Thank you for the help and points learnt.
 
Sam Mathai Chacko said:
Clever one Haseeb. You've given two solutions by just typing down one. Here's the other one

=SUMIF(G:G,"<="&A2,H:H)-SUMIF(G:G,"<"&A1,H:H)
Click to expand...
As I read it, what you are doing is to say count everything that is less than the last acceptable date then subtract anything less than the first acceptable date. Correct? Thank you all for the input. I am learning!.
 
You must log in or register to reply here.
Back
Top