Forum Discussion

Joesw775's avatar
Joesw775
Copper Contributor
Feb 20, 2022
Solved

Make summary of specific data

Hello,

 

I have a question about how I can best make a summary with the =sum function. 

I'll do my best tying to explain what I wish to achieve with this so:

In the cells (G3:G53) I can manually type a county's name such as China or Germany.

Then I have on (O18) Total China: ... (O19) Total Germany: ... and on (O20) Total Other: ...

 

Is there a way so that if I type Belgium in G5 that this adds up on (O20) and thus if I type China on G6 that it doesn't add up on (O20)

I already made it that it checks For China on (O18) and Germany on (O19)

 

Sorry for my English but I hope you can understand.

 

Thank you

excel
  • OliverScheurich's avatar
    OliverScheurich
    Feb 21, 2022

    Joesw775 

    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))

     

9 Replies

    • Joesw775's avatar
      Joesw775
      Copper Contributor
      Feb 20, 2022
      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 clearer

Resources