0

cordial greetings

I am trying to obtain the sum of the number of males taking into account an age range that I set previously.

Picture

For example, I want to know how many males, between the ages of 3 and 12, there were in 2018. For this, I have proposed the following formula:

=SUM(SUMIF(B2:V2,{"Mens_3","Mens_4","Mens_5","Mens_6","Mens_7","Mens_8","Mens_9","Mens_10","Mens_11","Mens_12"},B3:V3))

One of the many problems I have is that this formula is quite long, and on the other hand, if I want to modify the age range, for example: men between 5 and 10 years old, I have to modify the criteria of the formula.

I have tried with the SUMPRODUCT function, but I did not succeed.

Could you please let me know if there is a simpler way to perform the calculation I require?

Thank you for your attention

Improve this question
1
  • Just as a comment: You can edit Excel functions in Notepad, so they have hard returns, to make them more readable. If you then copy paste them into one cell, they will retain that page-layout or page-format. They do not need to be one long string.
    – GwenKillerby
    Commented Mar 26, 2022 at 8:04

1 Answer 1

Reset to default
1

Try the formula:

=SUMPRODUCT((NUMBERVALUE(REPLACE($B$1:$O$1,1,5,""))>=NUMBERVALUE(LEFT(B$7,FIND("-",B$7)-1)))*(NUMBERVALUE(REPLACE($B$1:$O$1,1,5,""))<=NUMBERVALUE(REPLACE(B$7,1,FIND("-",B$7),""))),$B$2:$O$2)

Drag it to fill the other cells.

You can shorten it

LET(x,NUMBERVALUE(REPLACE($B$1:$O$1,1,5,"")),y,FIND("-",B$7),SUMPRODUCT((x>=NUMBERVALUE(LEFT(B$7,y-1)))*(x<=NUMBERVALUE(REPLACE(B$7,1,y,""))),$B$2:$O$2))

Result

Improve this answer
9
  • Great graphics! Nice, with what software did you make that, if I might ask?_____ BTW, not to be overly nit-pickish here, ;) but your first solution is 191 characters long, and your shortened 2nd, 154 characters. The OP's original formula is 120 characters. So, 159% and 128% respectively. :P :D
    – GwenKillerby
    Commented Mar 26, 2022 at 7:58
  • 1
    @GwenKillerby The software is Licecap (you can search for it, plus it is free). Regarding the characters count, yes right now, mine is longer (even the shorten one). However if the OP has more columns, mine might be shorter in that case (I might be wrong though).
    – Reddy Lutonadio
    Commented Mar 26, 2022 at 8:03
  • Sadly the link i found had a version with a virus. see here virustotal.com/gui/file/…
    – GwenKillerby
    Commented Mar 26, 2022 at 12:59
  • @GwenKillerby Not sure if I should trust Gridinsoft on this one as there are the only that detect a possible virus. Bitdefender and Kasperky that I use don't detect anything and I trust them more. However you should be the one taking the edcision on whether to download and use it :)
    – Reddy Lutonadio
    Commented Mar 26, 2022 at 13:31
  • Dear @ReddyLutonadio thank you very much for your valuable information, it has been useful and has helped me to get an alternative way to find a solution to what I have been looking for, I appreciate your collaboration and attention to solve my doubts The solution I found with your help, I share it in the following line =SUMPRODUCT((MID($B$1:$O$1,1,FIND("_",$B$1:$O$1)-1)="Mens")*(MID($B$1:$O$1,FIND("_",$B$1:$O$1)+1,10)+0>=MID(B$7,1,FIND("-",B$7)-1)+0)*(MID($B$1:$O$1,FIND("_",$B$1:$O$1)+1,10)+0<=MID(B$7,FIND("-",B$7)+1,LONG(B$7))+0)*($B$2:$O$2))
    – Camila Sanchez
    Commented Mar 27, 2022 at 0:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .