Forum Discussion
Make summary of specific data
With LET and UNIQUE this formula seems to work in my Excel online sheet. Duplicates other than China and Germany are counted only once. The formula can be adapted if you want to count other countries regardless of duplicates.
=LET(arr,G3:G53, unique,UNIQUE(arr), SUM((unique<>N18)*(unique<>N19)*(NOT(ISBLANK(unique)))) )Without Office365 or 2021 or Excel online this works in my sheet:
=SUM(MMULT((G3:G53<>N18)*(G3:G53<>N19)*(NOT(ISBLANK(G3:G53))),ROW(1:1)^0))However this formula only counts the total number of occurances of other countries regardless of duplicates.
In both examples i applied following formulas for number of occurances of China and Germany. Enter these formulas with ctrl+shift+enter if you don't work with Office365 or 2021 or Excel online.
=SUM(N($G$3:$G$53=N18))=SUM(N($G$3:$G$53=N19))
Joesw775 Not entirely sure what you are after but perhaps SUMIF is what you need. See attached.
- Hi,
Thanks for your answer. This is not exactly what I'm trying to achieve. I added an example.xlsx to give an example.
So in G I need to type whatever I want. When I type something else than China or Germany it needs to add 1 up to the "Total others:" or (P6) in this case, so that I know it is a different county than that I specified. But when I type something in G that it does recognize like China, it needs to not add up to "Total others:" but to "Total China:".
Hope this made it a bit clearerJoesw775 There's no example.xlsx, unfortunately.
Riny_van_EekelenYep sorry. Can't find the button to attach docs... here is a photo I guess.
Spoiler(P4) is just: =sum.if(G3:G53;"China")
