Question in SUMs & SUMIF

Guys Hi,

Wanting to know,

Can we breakup the view of the Sumif?

Eg :

Company A has given us 2 orders,

Through sum if, we can let the equation to find the name of company and then give us the value in total to appear.

My question is there any possibility to get the breakup to be shown as

=45000+50000

in case we click the exact cell,

But at the moments we don't click the cell,

It could just show the total.

Thank you..

@ Whizbang

You have no single clue how much of a help you have been.

This is great !

This is EPIC !

This is making my life so easy !!!

Thanks a lot Bro.

So let me thank you.

=THANKYOU*1000000



Just help me out how should I now add this to my sheet to get it working?

Like what should be looking at when I do the changes.

Thank you ! Thank you ! Thank you !

P.S - In case I add sheets or remove any sheet without the Sheet where VBA is written to, can it stop working by any chance, etc, should I have to be cautious of?

@ Whizbang

Hi Bro,

Good Morning to you from Qatar !

First of all thank you for the trouble you took for the coding.

I checked out the excel sheet attached but I did not see the breakup of test1 values which had been added showing in it.

Not sure if you wanted me to place the VBA Coding, as I have no clue how VBA works.

I can say my requirement again.

In your worksheet,

I saw at the left hand side you have entered a number of values named after test1, test2. etc.

and the SUMIF totals in the right.

I want once we go to the Cell D2, Not to show the coding of sumif but to appear what are the values it added.

Forgive me for the trouble.

Thank you..

@ Glenn Kennedy

Hi brother, greetings from Qatar.

I've enabled Macros, still this is what appears when I go from D2 to I2


=SUMIF($A$1:$A$39,D$1,$B$1:$B$39)

Just the equation.

Not the values it has added up...

No buddy,

I don't.

I have no clue why...

Untitledxlsheet.png

Any suggestions with my settings I can check with?

@ Whizbang

You have no single clue how much of a help you have been.

This is great !

This is EPIC !

This is making my life so easy !!!

Thanks a lot Bro.

So let me thank you.

=THANKYOU*1000000



Just help me out how should I now add this to my sheet to get it working?

Like what should be looking at when I do the changes.

Thank you ! Thank you ! Thank you !

OMG !!!

That is so cool...


Now what am I suppose to do?

That is exactly what I need !!!

GREAT GREAT GREAT GREAT GREAT !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


FINALLY IT WORKS....

FINALLY...


I chose Disable all macros with Notification. Which is the Default.

But that is what it was before.

I changed back to the default because nothing worked...

Then suddenly the Pop-up came in.


This is Epic.

My final question,

where and how should I change this Macros, for me to get my work done.

I have 9 sheets in my workbook I need this to apply & and I need to apply to the sheet 1, which is named "SUM OF CHEQUES"

Oh I see you're an Irish man !
Love Ireland. Seen pictures and It is a nice country.

Oneday I will visit there.

Ireland the motherland of My favorite & best in the world Standup Comedian. Late. George Carlin ! Love him so much.

Bro, any clues how can I re-module this to a completely different sheet?

P.S - You got 4,300/- posts. Man you outto be eating Excel for Lunch and Dinner.

Wish I had deep knowledge in Excel. Still learning, Have to study VBA Soon too !

Lol,

"Just 'cos you've Xthousand posts doesn't mean you have to know ANYTHING about VBA" - True.

One of Carlin favorite is "Time", how he speaks about time is really funny. "You wait, there it comes, it is almost here, Here it is and gone, (shu)-imitataes sound- it's gone, But it was just there............where did it go? " lol.

Matter is solved as per the workbook attached.

I just don't know how to get this work in my sheet at work...



Hope you enjoy your walk. - I love cold and wet Climates.

I will be starting to workout from tomorrow. Found a gym close by.

It is my weekend and I only use my tablet during the weekends. I'll reply with how to make this work on any sheet on Monday.

@ Whizbang,

Thank you very much bro, will be waiting for your reply.

Thank you again.

Sorry for all the trouble.

Tc and Enjoy the weekend.

Here is the code to put in your workbook to make it work on any sheet.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim rngIf As Range
    Dim varCriteria As Variant
    Dim rngSum As Range
    
    If Target.Count = 1 Then
        If Left(Target.Formula, 7) = "=SUMIF(" Then
            Set rngIf = Range(Mid(Target.Formula, 8, InStr(1, Target.Formula, ",") - 8))
            If InStr(InStr(1, Target.Formula, ",") + 1, Target.Formula, ",") = 0 Then
                varCriteria = Replace(Mid(Target.Formula, _
                    InStr(1, Target.Formula, ",") + 1, _
                    InStr(1, Target.Formula, ")") - InStr(1, Target.Formula, ",") - 1), """", "")
                If Not IsError(Application.Evaluate(varCriteria)) Then
                    varCriteria = Application.Evaluate(varCriteria)
                End If
                Set rngSum = Range(Mid(Target.Formula, 8, InStr(1, Target.Formula, ",") - 8))
            Else
                varCriteria = Mid(Target.Formula, _
                    InStr(1, Target.Formula, ",") + 1, _
                    InStr(InStr(1, Target.Formula, ",") + 1, Target.Formula, ",") - InStr(1, Target.Formula, ",") - 1)
                If Not IsError(Application.Evaluate(varCriteria)) Then
                    varCriteria = Application.Evaluate(varCriteria)
                End If
                Set rngSum = Range(Mid(Target.Formula, _
                    InStr(InStr(1, Target.Formula, ",") + 1, Target.Formula, ",") + 1, _
                    InStr(1, Target.Formula, ")") - InStr(InStr(1, Target.Formula, ",") + 1, Target.Formula, ",") - 1))
            End If
            Call ShowValues(rngIf, varCriteria, rngSum, Target)
        End If
    End If
End Sub

Private Sub ShowValues(rngIf As Range, varCriteria As Variant, rngSum As Range, rngTarget As Range)

    Dim Result As String
    Dim Operator As String
    Dim Operators As Variant
    Dim i As Long
    
    Operator = "="
    Operators = Array(">=", "<=", "<>", "<", ">")
    
    For i = 0 To 4
        If InStr(1, varCriteria, Operators(i)) Then
            Operator = Operators(i)
            varCriteria = Replace(varCriteria, Operator, "")
        End If
    Next i
    
    varCriteria = Replace(varCriteria, "&", "")
    
    If Not IsError(Application.Evaluate(varCriteria)) Then
        varCriteria = Application.Evaluate(varCriteria)
    End If
    
    For i = 1 To rngIf.Count
        If Operator <> "=" Then
            If Application.Evaluate(rngIf(i, 1).Value & Operator & varCriteria) Then
                Result = Result & Application.Rept(" ", Len(Format(rngTarget.Value, "#,##0.00")) + 2 - Len(Format(rngSum(i, 1).Value, "#,##0.00"))) & Format(rngSum(i, 1).Value, "#,##0.00") & Chr(10) & Chr(13)
            End If
        Else
            If rngIf(i, 1).Value = varCriteria Then
                Result = Result & Application.Rept(" ", Len(Format(rngTarget.Value, "#,##0.00")) + 2 - Len(Format(rngSum(i, 1).Value, "#,##0.00"))) & Format(rngSum(i, 1).Value, "#,##0.00") & Chr(10) & Chr(13)
            End If
        End If
    Next i
    
    Result = Result & Application.Rept("-", Len(Format(rngTarget.Value, "#,##0.00")) + 2) & Chr(10) & Chr(13) & Application.Rept(" ", 2) & Format(rngTarget.Value, "#,###0.00")
    
    MsgBox Result

End Sub

How to install your new code

1. Copy the Excel VBA code above
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Double-Click the ThisWorkbook module in the left-hand navigation panel
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Hi Bro,

Yes, I am replying extremely late, sorry about that...

I was on my annual vacation...

So just wanted to get my head out of the office mentality.

I just did exactly as you said,

But it did not appear, when I started thinking what might have been the cause, I remembered instead of SUMIF, I used SUMIFS, as I had few more criteria which I wanted filtered.

Is this Macro coding possible to change to SUMIFS?

Plus, when I tried using the SUMIFS, I encountered an error as well,

I need a hand if possible.

=SUMIFS('All Cheque'!$E$8:$E$2942,'All Cheque'!$B$8:$B$2942,'Sum of Cheques'!C6,'All Cheque'!$I$8:$I$2942,'Sum of Cheques'!$D$5,'All Cheque'!$G$2049:$G$2941,">"&'Sum of Cheques'!$D$3,'All Cheque'!$G$2049:$G$2941,"<"&'Sum of Cheques'!$E$3)

is the equation I have used.

The equation works, well when I take out the BOLD,UNDERLINED,RED COLOR coding....

BUT when I add it, an error appears.

These are the data how I have tried doing it,

PRA.

Thank you..

Originally Posted by XOR LX

Hi.

Ranges passed to SUMIFS must be of an equal dimension.

Regards

Thank you for your feedback,

But the issue starts right when I try to add the date to the transition.....

The below works really well....

=SUMIFS('All Cheque'!$E$8:$E$2942,'All Cheque'!$B$8:$B$2942,'Sum of Cheques'!C6,'All Cheque'!$I$8:$I$2942,'Sum of Cheques'!$D$5,

But I just need to shrink this too by a time period....

Any ideas how to do it?

You may open the above attached excel,

you will see exactly what I am trying to do....

Thank you again...

Hi Bro,

Yes, I am replying extremely late, sorry about that...

I was on my annual vacation...

So just wanted to get my head out of the office mentality.

I just did exactly as you said,

But it did not appear, when I started thinking what might have been the cause, I remembered instead of SUMIF, I used SUMIFS, as I had few more criteria which I wanted filtered.

Is this Macro coding possible to change to SUMIFS?

Plus, when I tried using the SUMIFS, I encountered an error as well,

I need a hand if possible.

=SUMIFS('All Cheque'!$E$8:$E$2942,'All Cheque'!$B$8:$B$2942,'Sum of Cheques'!C6,'All Cheque'!$I$8:$I$2942,'Sum of Cheques'!$D$5,'All Cheque'!$G$2049:$G$2941,">"&'Sum of Cheques'!$D$3,'All Cheque'!$G$2049:$G$2941,"<"&'Sum of Cheques'!$E$3)

is the equation I have used.

The equation works, well when I take out the BOLD,UNDERLINED,RED COLOR coding....

BUT when I add it, an error appears.

These are the data how I have tried doing it,

PRA.

Thank you..