Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
So I have this formula;
=COUNTA(UNIQUE(FILTER(EXPORT!D2:D140000,EXPORT!O2:O140000=Sheet3!AB12)))
What I’m trying to do is count the number unique numbers in column D based on a match of column O and AB12. Which is seems to do, but when it doesn’t find a match it outputs “1” when I want it to output “0”. When entering this formula there is an option for [empty cell] right at the end but I’m not getting it to work so far.
Any suggestions? I’m sure it’s something stupid I’ve missed.
I also have this formula for the same job;
=SUMPRODUCT(IF(($D$2:$D$400<=$Q2)*($D$2:$D$400>=$P2), 1/COUNTIFS($D$2:$D$400, "<="&$Q2, $D$2:$D$400, ">="&$P2, $B$2:$B$400, $B$2:$B$400), 0))
But setting that to the required range kills my laptop.
I don't quite understand what you're trying to do, but if you have no matches, does that we return zero is all cells? Then you count the the number of unique values, and there's one unique value, zero? So COUNTA returns 1.
If it doesn’t find a match it returns the value 1
I want it to return 0 if it finds no match
Im looking to identify the number of unique occupancies based on a criteria. Ignore the second formula, it doesn’t work now I’ve thought it through a bit more.
Could you put the first formula inside an IF function to return 0?
in SQL it would be an ISNULL(field, 0)
So something like this? https://support.microsoft.com/en-gb/office/using-if-to-check-if-a-cell-is-blank-dff4eda1-6187-4b83-b7f6-4c3c0a1e2188?ui=en-us&rs=en-gb&ad=gb
or
https://docs.microsoft.com/en-us/office/troubleshoot/excel/use-formula-evaluate-blank-cell
Oh, that could be it.
Well, it’s worth a shot. I’ll drink more coffee then try it.
Not in front of excel, and I use excel rarely nowadays but...
row 2 =iferror(countif(EXPORT!$D$2:$D$140000,o2&$ab$12),0)
row 3 =iferror(countif(EXPORT!$D$2:$D$140000,o3&$ab$12),0)
Drag down the formula
Then in the column next to these formula, divide the answer to the formula by 1. Then sum that column. ( Might need an iferror around that division formula if some come back as zero )
iferror in excel is like isnull in sql
Might give you want you want.
=COUNT(UNIQUE((EXPORT!$O$2:$O$140000=$AB$12)*(EXPORT!$D$2:$D$140000)))
I want it to return 0 if it finds no match
I understand that. I think it is returning 0 matches - but you're not asking it to display how many matches, you're asking it to count how many unique values of matches there are. It's found a value of zero, and no other values; that's one unique value so it returns 1.