Excel combination of SUMIF and SUBTOTAL while SUM of column based on the column TEXT

[Jyotirmaya]

I have 1000+ rows in excel, in all rows of Column A there are only three texts that is "DEMAND" "COLLECTION" "BALANCE"

I want to calculate the column F total based on the text of column A.

for example

1 DEMAND 10
2 COLLECTION 20
3 BALANCE 50
4 DEMAND 80
5 DEMAND 10

1 2 3 4 5 are the row numbers and values are supposed in column F, then I want the result of DEMAND total is 100 as 10+80+10. I dont want to filter data. what should be the formula ??


F10 = SUM(IF(A1:A10="DEMAND")*SUBTOTAL(9,INDIRECT(F1:F10)))

this is what I have tried but not working some modification required

 

[Aladin Akyurek]

You seem asking for...

=SUMIFS(F:F,A:A,"DEMAND")

 

[Jyotirmaya]

You seem asking for...

=SUMIFS(F:F,A:A,"DEMAND")

It was so easy thanks

Function SumByFontColor(MyRange As Range, Optional MyColor As Range)
    Dim rng As Range
    Dim Col As Long

    Application.Volatile

    If MyColor Is Nothing Then
        Col = Application.Caller.Font.Color
    Else
        Col = MyColor(1).Font.Color
    End If

    SumByFontColor = 0
    For Each rng In MyRange
        If rng.Font.Color = Col Then
            SumByFontColor = SumByFontColor + rng.Value2
        End If
    Next rng
End Function

I am using the above code to determine red and black font color totals.
I am using =sumbyfontcolor by selecting the cell font color I am getting the results
in the balance total I want the results by color so how to assemble the both code ??
=SUMIFS(F:F,A:A,"BALANCE") + =sumbyfontcolor

 

[Aladin Akyurek]

You are welcome. Hope somebody else into VBA will take up your color question.