Book1.xlsx
I need to categorise the S/N by quarter.
I don't want to input the S/N in Table 2 manually. I need a formula for it as this is only part of the data that I need to categorise.
I need the S/N listed by the quarter they came in (Date In).
If someone can please help.
i have attached a photo how the result should look like in the excel file but it should be done using formulas
If Anyone CAN Help
THANKS
Last edited by Scubed; 09-15-2014 at 10:28 PM.
Hi Scubed, welcome to forum.
-Forum Rule-
Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
Please upload your sample workbook, so you can easy get answer/response.
-If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".
-Always upload a workbook before start your question
To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
+++ If my answer(s) helped you, please add me reputation by click on * +++
Can you upload a small Excel file with the data and desired results that you had in your pre-edit post? I'm not able to read *.PNG files. A workbook could be helpful.
FR
Edit: wenqq3 was ahead of me.
Re: Excel formula sumarizing items over quarters of year
Hi,
welcome to this forum..
Count
Formula:
Please Login or Register to view this content.
S/N (Array formula, so Use CTRL + SHIFT + ENTER to enter the formula)
Formula:Please Login or Register to view this content.
check the attached file..
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
Re: Excel formula sumarizing items over quarters of year
check the attached file..
Hi Vikas,
Like what you've done, but noticed that you have duplication of s/n records such as 77701, 77702 because they fall in months, 3,6,9,12.
For the Quarter couldn't you use:
=YEAR(B2)&"-"&"Q"&ROUNDUP(MONTH(B2)/3,0)
Put that in a column down the side of your data. (The above has the first date in cell B2 and will need modifying accordingly). The advantage of putting the year first is that it's easier to sort then! You could probably build this formula into the array formula, but I'm not going to try as my brain is excel'd out at the mo!
Actually the trick lies in this part only...
Formula:Please Login or Register to view this content.
When entered as array formula, it returns the array {1;1;0;0;1;0;1;1;1;1;0;1;1;0;1}
What countifs doing is that it counts each month in Range $K2, each Month in $L2 and each Date in M2 with there respective "<=" & ">=" conditions,
and returns 1 if it fulfills the conditions..
Same way, it test the condition for each element in Range $I$2:$I$16 and returns the array of ones and zeros ie {1;1;0;0;1;0;1;1;1;1;0;1;1;0;1}
Then I used if condition to return the row no. of the respective true(ie 1) and False for respective 0s ..
Small function with Column(A$1), when dragged horizontally, returns the 1st , 2nd, 3rd small row no. ..
and Index uses this row no. to output the respective element in index provided..
I hope I have been able to explain it well..
Moreover, you can use FORMULA AUDITING to understand it more....
Regards,
is it possible that that all relevant the S/N can be just posted in just 1 cell instead of posting each S/N in different cells.
Hi Vikas, there is duplication of S/N in different quarters though...
duplicates.png
Yeah it is possible ..
but you have to use a udf ie CONCATALL(RangeOrArray,Delimiter)
Formula:Please Login or Register to view this content.
Check the attached file..
Last edited by Vikas_Gautam; 09-15-2014 at 11:45 PM. Reason: Correction as Cerbera said..
hey vikas
quick question if this is possible
if D1>1/1/2011,D18<31/3/2011 then show the corresponding S/N's from coloumn A to show up in a particular cell.
man you are an advanced use of excel i am just a begginer
Still got duplicates in there... but no one seems to listen to me
cerbera could you show us how you would do it by getting rid of the duplicates mat your code is a bit easier thanks in advance
Re: Excel formula sumarizing items over quarters of year
No its not possible with countifs.. as it demands Range as a parameter..
however you can use array multiplication instead.. but it is slower than countifs.. thats why I prefer using countifs with some helper columns..
However the alternative would be..
Formula:Please Login or Register to view this content.
check the attached file..
i only requested this because i was having troble with the formulas you used before.
i am just a beginner at excel just started a few days ago
but then again thanks man
sorry about the reputation thing i didnot know until you told me
Does this layout work?
If so I'll download the file.
The formula is:
Formula:Please Login or Register to view this content.
what is the difference between my table and your table
Book1 (4)_2 (1).xlsm
Re: Excel formula sumarizing items over quarters of year
The CONCATALL Code is here in this file..
Download it and press alt + F11
select Module1 and you will find the code..
Code..
Please Login or Register to view this content.
Re: Excel formula sumarizing items over quarters of year
its working now ..
have a look...
Amazing use of countifs (1).xlsx
ok i did use the helper coloumns
quick question
is it possible to put all the S/N in one cell instead of putting them in individual one
i have attcahed workbook if you can have a look
Re: Excel formula sumarizing items over quarters of year
Without UDF..
check the attached file..
Just testing. Can't seem to access page 2.
edit: OK works, now.
THis is page 2, Flame
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
So it IS!Originally Posted by FDibbins
THis is page 2, Flame
Got it. Used your tip. That's how post #23 got here. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks