Whats wroing with Sumifs formula.

asked 2019-04-19 12:35:15 +0200

Sums gravatar image

Hi, whats wrong with this fromula. Correct result should be 30.8% but it returns 0%

=SUMIFS($Q$5:$Q$25,$P$5:$P$25,">=4",$P$5:$P$25,"<=6")

Thank you

edit retag flag offensive close merge delete

Comments

Without data sheet it is hard to diagnose, but one thing to consider. Percentages are - in general - less than "1" e.g. 30% means in fact 0.30, thus, if your values in $P$5:$P$25 are percentages lower than 100% your formula tries to find if-values between 4 (=400%) and 6 (=600%) and nothing (=0%) is summed. Could you upload an anonymized sample file?

Opaque gravatar imageOpaque ( 2019-04-19 12:48:17 +0200 )edit

Thanks for reply, ill add it later on when i have more time

Q5:Q25 is the summed range containing percentages e.g 10%,20% etc. P5:P25 contains Integers. I wanted to sum the percentages in Q5:Q25 if the integers in P5:P25 were >=4 and <=6

Sums gravatar imageSums ( 2019-04-19 14:46:43 +0200 )edit

Please also add yor OS and LibreOffice version. So far I have checked your formula and if you have integers in column P, it works for me on:

Version: 6.2.3.2
Build ID: aecc05fe267cc68dde00352a451aa867b3b546ac
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kde5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded

I pretty much assume that your integers are not integers but text looking like integers.

Opaque gravatar imageOpaque ( 2019-04-19 14:57:19 +0200 )edit