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

and if the cell is not empty...

W

Wulluby

Member
I have 2 formulas that appear to be behaving differently every alternate week, not so much the formula but the part at the end.

=IF(AQ$2="","",IF(SelectedPeriod=$D22,(COUNTIFS('sheet1'!$C$2:$C$1000,"="&BR$2,'sheet1'!$H$2:$H$1000,"<>"&"")),""))

"<>"&"" being what is giving me issues, when I put the formula together this was working and I had a count where cells were blank in 'sheet1'!$H$2:$H$1000 (among the other conditions).

Cut to the week following when I found that the results were counting blank cells. Scratching my head thinking that this was fixed the week before I found that replacing "<>"&"" with ">""" worked, blank cells were no longer counted.

Cut to the week after this and again I found that the results were once again counting blank cells so I changed back to "<>"&"" to fix the issue.

Cut to the week after this...well I think you can guess. I've been bouncing the end of that formula back and forward from week to week, the data comes from the same location each week which is an export from the same source each week and although the data changes week in week out I can't imagine why my formula seems to count blanks.

I say this changes every week, it is every week that I run the reports and then put them through this and it is at that point that I identify the change. At the same time, SelectedPeriod which is a date selected from validation is manually increased, ($D22 in that formula being in a column of dates which also feeds the data validation).

Any pointers?

Thanks
 
Hi Wulluby,

After reading your problem, and without actually looking at the file structure, my head had started scarching ;).
Kindly, post a sample file.

Regards,
 
Somendra Misra said:
Hi Wulluby,

After reading your problem, and without actually looking at the file structure, my head had started scarching ;).
Kindly, post a sample file.

Regards,
Click to expand...
I was dreading the request for a sample file, problem being the original file contains a lot of sensitive data although I understand the need for it in this case.

I will create a sample document and see if I can get it to emulate the issue.

Thanks
 
I assume SelectedPeriod is a Named Formula

Can you tell us what it is ?
 
Hi Hui

SelectedPeriod is a cell, named range. The user selects a reporting period from a drop down in SelectedPeriod. $D22 will hold a reporting period in the format of 08 Feb - 14 Feb 14. Column D holds the reporting periods that SelectedPeriod looks at with Data Validation.
 
Without seeing some data I suspect there is a space somewhere messing it up either in a cell or a blank cell

I'll wait to see a sample file
 
*groan* and just to confuse matters, let me add a correction: =IF(AQ$2="","",IF(SelectedPeriod=$D22,(COUNTIFS('sheet1'!$C$2:$C$1000,"="&AQ$2'sheet1'!$H$2:$H$1000,"<>"&"")),""))

Will get a sample up today.
 
Very simplified sample attached.

I haven't got it to emulate the problem so I don't know how much this will help, what is interesting though is that if I use either "<>"&"" or ">""" I get the same result, whereas on my production file it does not.

On the production file one will count all cells blank or with data, while the other will only count those cells which contain data. And which one does as expected varies which is what is completely bamboozling me.

You will notice the dates available in the selected period doesn't pick up all the periods in that column, each week I manually increase that so that the users can't look forward into dates that haven't been reported on yet. I don't know if that is relevant at all.
 

Attachments

  • Bug Sample.xlsx
    11.3 KB · Views: 3
Hey, no the results there is correct, it is counting where data in Import > header 3 matches the header above the formula and where there is also data under Import > Header 8.

Issue I have is that in the production document the blanks are counted when they should not be. To fix the problem I have to change the end of the formula "<>"&"" with ">""" or vice versa so that it does not count the blanks. This report gets compiled once a week and I find I have to change that formula every week backwards and forwards, for reasons I can't figure.
 
@Wulluby

Try Using below formula
=IF(E$1="","",IF(SelectedPeriod=$D3,(COUNTIFS(Import!$C$2:$C$1000,E$1,Import!$H$2:$H$1000,"<>")),""))
OR
=IF(E$1="","",IF(SelectedPeriod=$D3,(COUNTIFS(Import!$C$2:$C$1000,E$1,Import!$H$2:$H$1000,"<>"&"")),""))

Regards,
 
Or may be try this

=IF(E$1="","",IF(SelectedPeriod=$D3,(COUNTIFS(Import!$C$2:$C$1000,"="&E$1,Import!$H$2:$H$1000,"="&"*")),""))
 
One small doubt, whatever period you select the result will remain same for every row. Is it OK? I mean it should have some relation with dates also.
 
That is why I manually change the data validation period so that users can't select in the future and think they are looking at something that hasn't happened. I import the data into multiple sheets, select the relative reporting period then paste values only in the YTD data so that it becomes static.

Your first options tidies up my criteria1, thanks.

In the production doc it's the same issue with the end though in that blanks are being counted. I changed the end to ">""")),"")) in order to fix this but at some point in the future, (next week if the last month is anything to go by I will need to change that back to "<>")),""))).

"="&"*")),"")) is a new one, I haven't tried that one in the mix yet. Again it seems to be counting the blanks as well as the cells with data. Will be interesting to see how that one plays out in the upcoming weeks.
 
Did the third option is also giving the wrong result. As in sample file all the three options are giving right result what ever period you select. And if it is giving wrong results in your production sheet than there is something wrong in that sheet which need an insight analysis.
 
Yeah, this is a nightmare to troubleshoot as I can't share the data being imported. On one week those blank cells will be counted as having contents by ">""")),"")) but on another week they will be counted by "<>")),""))). Only thing that changes is the data validation selection and the input data, so I'm guessing it can only be the input data which is odd considering it comes from the same source each week.

I shall try and identify a pattern in the meantime, thanks for your input, it has been much appreciated.
 
You must log in or register to reply here.
Back
Top