# SUMIF and CountIF formula



## Justmetrying (Dec 27, 2022)

Hi does anyone know if I could get these output without the use of array formula?

I want to count the number of N that appears on 1st Dec and if the C.Code is the same it will not double count.

Current formula : {=SUM(IF(($B3=$A$9:$A$18)*($B$9:$B$18=$C$1),1/COUNTIFS($A$9:$A$18,$B3,$C$9:$C$18,$C$9:$C$18,$B$9:$B$18,$C$1)),0)}
Hope to hear from some experts soon.


----------



## jasonb75 (Dec 27, 2022)

There may be some alternative formulas that you could try but they would all be array formulas of some kind.

Unless the real world version of your example is much bigger and too slow calculating the best theory to work on here is 'if it ain't broke then don't try to fix it'.


----------



## shinigamilight (Dec 27, 2022)

=COUNT(UNIQUE(FILTER(A2:C11,(A2:A11=44896)*(B2:B11="N"))))

if you have access to unique and filter function this might work, I don't know if 2019 excel has it though.


----------



## Justmetrying (Dec 28, 2022)

jasonb75 said:


> There may be some alternative formulas that you could try but they would all be array formulas of some kind.
> 
> Unless the real world version of your example is much bigger and too slow calculating the best theory to work on here is 'if it ain't broke then don't try to fix it'.


Hi I do get value error sometimes but I have too many to individually pressing crt shift enter to resolve them T>T


----------



## Justmetrying (Dec 28, 2022)

shinigamilight said:


> =COUNT(UNIQUE(FILTER(A2:C11,(A2:A11=44896)*(B2:B11="N"))))
> 
> if you have access to unique and filter function this might work, I don't know if 2019 excel has it though.


Sadly I do not have it


----------



## jasonb75 (Dec 28, 2022)

Try this one instead, it should eliminate any possible errors that can occur

```
=SUM(IFERROR(1/COUNTIFS($A$9:$A$18,$A$9:$A$18,$A$9:$A$18,$B3,$C$9:$C$18,$C$9:$C$18&"",$B$9:$B$18,$B$9:$B$18,$B$9:$B$18,C$1),0))
```


Check the results after entering it, I may need to make some changes if it starts giving you obscure decimal results. I can't test it on a screen capture and have a nagging feeling that something is not quite right.


----------



## Justmetrying (Dec 29, 2022)

jasonb75 said:


> Try this one instead, it should eliminate any possible errors that can occur
> 
> ```
> =SUM(IFERROR(1/COUNTIFS($A$9:$A$18,$A$9:$A$18,$A$9:$A$18,$B3,$C$9:$C$18,$C$9:$C$18&"",$B$9:$B$18,$B$9:$B$18,$B$9:$B$18,C$1),0))
> ...


Hi I tried the value seems correct but still requires to be run as an array formula but Im trying to avoid that haha thinking if there is any better way to do this


----------



## jasonb75 (Dec 30, 2022)

Justmetrying said:


> Hi I tried the value seems correct but still requires to be run as an array formula but Im trying to avoid that haha thinking if there is any better way to do this


As I said before, any formula that you use for this would be an array formula of some kind. With the latest version of excel you could make it more efficient by using dynamic array functions as @shinigamilight demonstrated earlier but with 2019 you are limited to what you can do.


----------

