Apply forluma on formulated rows
Hello,
I have applied array formula on sheet 2 to automatically update data from sheet 1. Now I want to apply count operation on these fields in sheet 2. Unfortunately I am unable to sort the fields or I am able to apply count operation. Do you think Excel does not identify the fields.
Thanks in advance.
Hi
Difficult to say without seeing the workbook. Please upload.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Attached is the dummy sheet. Look at sheet3. The data is truncated from other sheet using array formula. I want to apply a count function on this data, but its results in 0.
Thank you
The date is the problem. Column B seems to be specific days, for example, B16 is 12/7/2013. Your "=countif" formula is just searching for "2013", so that's why it can't find any matches. If you change B16 to 2013, L9 will return a result of 1. So you need to fix your dates.
Thank you,
I got it. But as I said I am automatically pulling data into this sheet from different sheet, and the date format in the main sheet is dd/mm/yyyy hr:mm:ss. I want to generate a chart based on just years like 2013 and 2014. I don't care the date and month. Any solution for this task?
Easiest way is to just insert a new Column C and enter the formula below. Then update your "=COUNTIF" formula to reference Column C instead of Column B.
Formula:
Please Login or Register to view this content.
And obviously copy down the formula from C2 all the way down.
Try this......
In L7
and copy down.
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
That worked perfectly well!!!
Thank you.
Here is a different approach. It is a Pivot Table. Pivot tables will not show values that are not in the data and therefore will not give a zero for where there isn't data. It will however use the dates as they arrive from your source if it is a real Excel date without having to isolate the year. You can also almost instantly change the year that you are interested in and the Application if there is more than 1. In addition....no formulae required.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks