Count, counta & countif

[Noobulon]

Hello everyone,

I seem to have trouble with a formula.

Here is an example of what i'm using to generate my list of averages (for blue only, table1) :

=IFERROR(AVERAGE(IF((Table1[Municipality]=B4)*(Table1[Street]=C4)*(Table1[[04-Jun-17]:[30-Jul-17]]<>""),Table1[[04-Jun-17]:[30-Jul-17]])),"")

Now, that formula works great for average, but i seem to be getting wrongs values when i want to count all the non-blank spaces, the spaces only with a "b" value and the ones with a "nb" value.

Here is what ma tables of results looks like:
STREETAVERAGE.PNG

And here is the table from witch I'm extracting information (Table1)
TABLE1111.PNG

Now i would like a Formula like in the first Picture, that i could drag down that would count all the "non empty spaces" under samples>blue , then count all the "nb" under nb>blue, and finally the "b" .

I've tried something like this but it count my entire blue Table1..

=IFERROR(COUNTA(IF((Table1[Municipality]=B4)*(Table1[Street]=C4)*(Table1[[04-Jun-17]:[30-Jul-17]]<>""),Table1[[04-Jun-17]:[30-Jul-17]])),"")

Thanks for your time

[xlnitwit]

Hi,

Perhaps
=IFERROR(SUM((Table1[Municipality]=B4)*(Table1[Street]=C4)*(Table1[[04-Jun-17]:[30-Jul-17]]<>"")),"")

[Noobulon]

That seems to work to count my non blank cells, thanks!!

Now is there a way to specify the formula to only count cells with "nb" ?

[AliGW]

Try this:

=IFERROR(SUM((Table1[Municipality]=B4)*(Table1[Street]=C4)*(Table1[[04-Jun-17]:[30-Jul-17]]="nb")),"")

[Noobulon]

Thank you AliGW, It seems i should of been using the SUM instead of COUNT.

TY again, this Forum is very helpful.

[AliGW]

You're welcome!