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))
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 clearer
Joesw775 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")
- That could be a COUNTA of the yellow range minus the sum of the two orange cells directly above.
- Ah yes that sounds like it should work. Do you know how to put the minus in the middle of the formula? Cause now i have "COUNTA(G3:G52;MIN(SUM(O18;O19)))" but this just count how many cells are empty and does not subtract O18 and O19 (added up).