issue regarding sumifs

[Former Member]

Hi all

in dashboard spreadsheet i have two columns A and B.

A consists of values that has to be Added to get total if corresponding values in B satisfies the condition.

the condition is that the value in B must be >200 and <300.

i tried sumifs but it is not supported in dashboard.

can someone please help me with the formula or any other alternative.

thanks in advance

[Former Member · Accepted solution]

Hi M Prasad,

You may use the below formula for adding in range.

SUMIF(A1:B10,">=100",B1:B10)-SUMIF(A1:B10,">=200",B1:B10)

above formula will add the values ranging from 100-200

Hope this helps !

Thank you 🙂

[former_member199155]

Hi Prasad,

Do you have fixed number of rows always in your data set ?

Thanks,

Vinod

[Former Member]

Hi,

Add 4 more columns. Each column will categorize the numbers in Column based on their category in B

Let's say Column B have the following values:  0-200,201-400,400-600,>600

Put the following labels (titles in columns C to F:

C1: 0-200    D1: 201-400     E1: 400-600      F1: >600

Add the following formula starting @ row 2 of Columns C to F

C2: if($B2 = C$1, $A1, "")

D2: if($B2 = D$1, $A1, "")

E2: if($B2 = E$1, $A1, "")

F2: if($B2 = F$1, $A1, "")

Then copy cells: C2:F2 to the rest of the rows

Finally, total each rows to get the numbers you need for each category

[Former Member]

Hi,

SUMIF Function is not supported in Xcelsuis.

Add one Extra column before "A" Lets Say "A-1"

Write If Else condition for "B" in Column "A-1".

=If(B11>200,If(B11 < 300, "Value > 200 & < 300",""),"")

Add Combo box with List of Values in "A-1". Set Default Selected  = "Value > 200 & < 300"

Source from A-B

Destination where ever you want for eg. Sheet2!B11-C11.

Now add sum function at B10 for B11-B1000(your range)

[Former Member]

thanks for the quick reply

actually my problem is to get sum of values in A for 4 ranges 0-200,201-400,400-600,>600

using your solution i cannot get this requirement.

please help me if there is any other alternative.

thank you

[Former Member]

Hi,

Do it in BI Query if you are using SAP BI.

Else Modify that if formula as per your need (For all four ranges ) .

And create Four Different Combo Box with different Destination & different default selection .

And in Separate Work sheet Map these values with simple = operator (=Sheet2!E10).