Help needed in one of excel problem.

G

Gaurav Verma

New Member
Joined
Mar 24, 2024
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Greeting! I am stuck on an excel/Google sheet problem. I have a sales team which maintains day wise payments collection in excel/Google Sheet. I have to count the number of payments in a month. Team leader fills the day wise payments, in few days, there are multiple payments against one team member..since they add payments in the same cell, if i use simple countif formula, it doesn't account for values prior results and gives count of only nonblank cells, which is incorrect. I have to count the numbers of payments against each team member prior results.
Screenshot 2024-03-24 133340.png
 

Attachments

  • Screenshot 2024-03-24 133449.png
    Screenshot 2024-03-24 133449.png
    22.3 KB · Views: 14
  • Screenshot 2024-03-24 133643.png
    Screenshot 2024-03-24 133643.png
    32.8 KB · Views: 12

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
These values like 13532, 1344, etc. as you add in formula bar have to be written in some cells. then you can sum the content of these cells by some kind of "filtering only these cells" to not include other cells. May be with SUMIF or other function.
 
Upvote 0
EDIT: Looking at your profile you are using 2010.
I don't think FORMULATEXT came in until 2013, If so the below won't work but please test.
Please note that I rarely use Google Sheets and so have no clue if it has a version of Formulatext (2nd Edit: apparently Google sheets does have a version of Formulatext)

You could put an empty row between each team member then use the formulas in in B2, B4 and B6, and rows 3,5 & 7 below.

Book1
ABCDEFGH
1Team memberSum01/03/202402/03/202403/03/202404/03/202405/03/202406/03/2024
2A12180017022
3400305
4B9130012220
5200340
6C1056013010
7603010
Sheet3
Cell Formulas
RangeFormula
B2,B6,B4B2=SUM(C3:$H3)
C2C2=12+1+2+3
F2F2=6+5+6
H2H2=5+2+3+5+7
C3:H3,C7:H7,C5:H5C3=IFNA(IF(C2<>0,LEN(FORMULATEXT(C2))-LEN(SUBSTITUTE(FORMULATEXT(C2),"+","")),0),0)+IF(C2<>0,1,0)
C4C4=7+6
F4F4=5+2+5
G4G4=6+3+5+8
C6C6=22+8+12+7+3+4
E6E6=5+3+5
 
Last edited:
Upvote 0
MARK858 said:
EDIT: Looking at your profile you are using 2010.
I don't think FORMULATEXT came in until 2013, If so the below won't work but please test.
Please note that I rarely use Google Sheets and so have no clue if it has a version of Formulatext (2nd Edit: apparently Google sheets does have a version of Formulatext)

You could put an empty row between each team member then use the formulas in in B2, B4 and B6, and rows 3,5 & 7 below.

Book1
ABCDEFGH
1Team memberSum01/03/202402/03/202403/03/202404/03/202405/03/202406/03/2024
2A12180017022
3400305
4B9130012220
5200340
6C1056013010
7603010
Sheet3
Cell Formulas
RangeFormula
B2,B6,B4B2=SUM(C3:$H3)
C2C2=12+1+2+3
F2F2=6+5+6
H2H2=5+2+3+5+7
C3:H3,C7:H7,C5:H5C3=IFNA(IF(C2<>0,LEN(FORMULATEXT(C2))-LEN(SUBSTITUTE(FORMULATEXT(C2),"+","")),0),0)+IF(C2<>0,1,0)
C4C4=7+6
F4F4=5+2+5
G4G4=6+3+5+8
C6C6=22+8+12+7+3+4
E6E6=5+3+5
[/RANGE
Click to expand...



Thank you for your help Marks. Formula worked for individual cells in Google sheets however since i am working on huge set of data it is cumbersome to add rows below each team members. I need to find something which is able to count + signs for a range of data.
 
Last edited by a moderator:
Upvote 0
You could do it with a macro if you were doing it in Excel so
Book1
ABCDEFGHIJKL
1Team memberSum01/03/202402/03/202403/03/202404/03/202405/03/202406/03/202407/03/202408/03/202409/03/202410/03/2024
2A180017022
3B130012220
4C56013010
Sheet2
Cell Formulas
RangeFormula
F2F2=6+5+6
H2H2=5+2+3+5+7
F3F3=5+2+5
G3G3=6+3+5+8
C2C2=12+1+2+3
C3C3=7+6
C4C4=22+8+12+7+3+4
E4E4=5+3+5


would turn into

Book1
ABCDEFGHIJKL
1Team memberSum01/03/202402/03/202403/03/202404/03/202405/03/202406/03/202407/03/202408/03/202409/03/202410/03/2024
2A12180017022
3400305
4B9130012220
5200340
6C1056013010
7603010
Sheet2
Cell Formulas
RangeFormula
B2,B6,B4B2=SUM(C3:$H3)
C2C2=12+1+2+3
F2F2=6+5+6
H2H2=5+2+3+5+7
C3:H3,C7:H7,C5:H5C3=IFNA(IF(C2<>0,LEN(FORMULATEXT(C2))-LEN(SUBSTITUTE(FORMULATEXT(C2),"+","")),0),0)+IF(C2<>0,1,0)
C4C4=7+6
F4F4=5+2+5
G4G4=6+3+5+8
C6C6=22+8+12+7+3+4
E6E6=5+3+5


with this macro

VBA Code: 
Sub Insert_row()
    Dim iRow As Long, LstRw1 As Long, LstRw2 As Long
    Dim LstCol As Integer
 
    Application.ScreenUpdating = False
 
    LstRw1 = Range("A" & Rows.Count).End(xlUp).Row

    For iRow = LstRw1 To 3 Step -1               '<~~ this is the magic
        Cells(iRow, 1).EntireRow.Insert Shift:=xlDown
    Next iRow
 
    LstRw2 = Range("A" & Rows.Count).End(xlUp).Row + 1
    LstCol = Cells(2, Columns.Count).End(xlToLeft).Column

    With Range(Cells(2, 3), Cells(LstRw2, LstCol))
        .SpecialCells(4).FormulaR1C1 = _
                    "=IFNA(IF(R[-1]C<>0,LEN(FORMULATEXT(R[-1]C))-LEN(SUBSTITUTE(FORMULATEXT(R[-1]C),""+"","""")),0),0)+IF(R[-1]C<>0,1,0)"
    End With
 
    Range(Cells(2, 1), Cells(LstRw2, 1)).SpecialCells(xlCellTypeConstants, 23).Offset(, 1).FormulaR1C1 = "=SUM(R[1]C[1]:R[1]C" & LstCol & ")"

    Application.ScreenUpdating = True

End Sub

but I have no idea what the code would be for Google Sheets.

Gaurav Verma said:
I need to find something which is able to count + signs for a range of data
Click to expand...
Just to clarify if you are going to post the question somewhere you need to count + signs within a formula which is within a range, counting the + signs in normal text in a range is relatively straightforward
 
Last edited:
Upvote 0
Solution
with this macro

VBA Code: 
Sub Insert_row()
    Dim iRow As Long, LstRw1 As Long, LstRw2 As Long
    Dim LstCol As Integer
 
    Application.ScreenUpdating = False
 
    LstRw1 = Range("A" & Rows.Count).End(xlUp).Row

    For iRow = LstRw1 To 3 Step -1               '<~~ this is the magic
        Cells(iRow, 1).EntireRow.Insert Shift:=xlDown
    Next iRow
 
    LstRw2 = Range("A" & Rows.Count).End(xlUp).Row + 1
    LstCol = Cells(2, Columns.Count).End(xlToLeft).Column

    With Range(Cells(2, 3), Cells(LstRw2, LstCol))
        .SpecialCells(4).FormulaR1C1 = _
                    "=IFNA(IF(R[-1]C<>0,LEN(FORMULATEXT(R[-1]C))-LEN(SUBSTITUTE(FORMULATEXT(R[-1]C),""+"","""")),0),0)+IF(R[-1]C<>0,1,0)"
    End With
 
    Range(Cells(2, 1), Cells(LstRw2, 1)).SpecialCells(xlCellTypeConstants, 23).Offset(, 1).FormulaR1C1 = "=SUM(R[1]C[1]:R[1]C" & LstCol & ")"

    Application.ScreenUpdating = True

End Sub

but I have no idea what the code would be for Google Sheets


Thank you for quick help mark. Taking inspiration from your last shared formula, i rather repeated the formula for 31 columns (depicting days) and it worked. =IFNA(IF(K2<>0,LEN(FORMULATEXT(K2))-LEN(SUBSTITUTE(FORMULATEXT(K2),"+","")),0),0)+IF(K2<>0,1,0) +IFNA(IF(L2<>0,LEN(FORMULATEXT(L2))-LEN(SUBSTITUTE(FORMULATEXT(L2),"+","")),0),0)+IF(L2<>0,1,0) +IFNA(IF(M2<>0,LEN(FORMULATEXT(M2))-LEN(SUBSTITUTE(FORMULATEXT(M2),"+","")),0),0)+IF(M2<>0,1,0) +IFNA(IF(N2<>0,LEN(FORMULATEXT(N2))-LEN(SUBSTITUTE(FORMULATEXT(N2),"+","")),0),0)+IF(N2<>0,1,0) +IFNA(IF(O2<>0,LEN(FORMULATEXT(O2))-LEN(SUBSTITUTE(FORMULATEXT(O2),"+","")),0),0)+IF(O2<>0,1,0) +IFNA(IF(P2<>0,LEN(FORMULATEXT(P2))-LEN(SUBSTITUTE(FORMULATEXT(P2),"+","")),0),0)+IF(P2<>0,1,0) +IFNA(IF(Q2<>0,LEN(FORMULATEXT(Q2))-LEN(SUBSTITUTE(FORMULATEXT(Q2),"+","")),0),0)+IF(Q2<>0,1,0) +IFNA(IF(R2<>0,LEN(FORMULATEXT(R2))-LEN(SUBSTITUTE(FORMULATEXT(R2),"+","")),0),0)+IF(R2<>0,1,0) +IFNA(IF(S2<>0,LEN(FORMULATEXT(S2))-LEN(SUBSTITUTE(FORMULATEXT(S2),"+","")),0),0)+IF(S2<>0,1,0) +IFNA(IF(T2<>0,LEN(FORMULATEXT(T2))-LEN(SUBSTITUTE(FORMULATEXT(T2),"+","")),0),0)+IF(T2<>0,1,0) +IFNA(IF(U2<>0,LEN(FORMULATEXT(U2))-LEN(SUBSTITUTE(FORMULATEXT(U2),"+","")),0),0)+IF(U2<>0,1,0) +IFNA(IF(V2<>0,LEN(FORMULATEXT(V2))-LEN(SUBSTITUTE(FORMULATEXT(V2),"+","")),0),0)+IF(V2<>0,1,0) +IFNA(IF(W2<>0,LEN(FORMULATEXT(W2))-LEN(SUBSTITUTE(FORMULATEXT(W2),"+","")),0),0)+IF(W2<>0,1,0) +IFNA(IF(X2<>0,LEN(FORMULATEXT(X2))-LEN(SUBSTITUTE(FORMULATEXT(X2),"+","")),0),0)+IF(X2<>0,1,0) +IFNA(IF(Y2<>0,LEN(FORMULATEXT(Y2))-LEN(SUBSTITUTE(FORMULATEXT(Y2),"+","")),0),0)+IF(Y2<>0,1,0) +IFNA(IF(Z2<>0,LEN(FORMULATEXT(Z2))-LEN(SUBSTITUTE(FORMULATEXT(Z2),"+","")),0),0)+IF(Z2<>0,1,0) +IFNA(IF(AA2<>0,LEN(FORMULATEXT(AA2))-LEN(SUBSTITUTE(FORMULATEXT(AA2),"+","")),0),0)+IF(AA2<>0,1,0) +IFNA(IF(AB2<>0,LEN(FORMULATEXT(AB2))-LEN(SUBSTITUTE(FORMULATEXT(AB2),"+","")),0),0)+IF(AB2<>0,1,0) +IFNA(IF(AC2<>0,LEN(FORMULATEXT(AC2))-LEN(SUBSTITUTE(FORMULATEXT(AC2),"+","")),0),0)+IF(AC2<>0,1,0) +IFNA(IF(AD2<>0,LEN(FORMULATEXT(AD2))-LEN(SUBSTITUTE(FORMULATEXT(AD2),"+","")),0),0)+IF(AD2<>0,1,0) +IFNA(IF(AE2<>0,LEN(FORMULATEXT(AE2))-LEN(SUBSTITUTE(FORMULATEXT(AE2),"+","")),0),0)+IF(AE2<>0,1,0) +IFNA(IF(AF2<>0,LEN(FORMULATEXT(AF2))-LEN(SUBSTITUTE(FORMULATEXT(AF2),"+","")),0),0)+IF(AF2<>0,1,0) +IFNA(IF(AG2<>0,LEN(FORMULATEXT(AG2))-LEN(SUBSTITUTE(FORMULATEXT(AG2),"+","")),0),0)+IF(AG2<>0,1,0) +IFNA(IF(AH2<>0,LEN(FORMULATEXT(AH2))-LEN(SUBSTITUTE(FORMULATEXT(AH2),"+","")),0),0)+IF(AH2<>0,1,0) +IFNA(IF(AM2<>0,LEN(FORMULATEXT(AM2))-LEN(SUBSTITUTE(FORMULATEXT(AM2),"+","")),0),0)+IF(AM2<>0,1,0) +IFNA(IF(AN2<>0,LEN(FORMULATEXT(AN2))-LEN(SUBSTITUTE(FORMULATEXT(AN2),"+","")),0),0)+IF(AN2<>0,1,0) +IFNA(IF(AO2<>0,LEN(FORMULATEXT(AO2))-LEN(SUBSTITUTE(FORMULATEXT(AO2),"+","")),0),0)+IF(AO2<>0,1,0) +IFNA(IF(AP2<>0,LEN(FORMULATEXT(AP2))-LEN(SUBSTITUTE(FORMULATEXT(AP2),"+","")),0),0)+IF(AP2<>0,1,0) +IFNA(IF(AQ2<>0,LEN(FORMULATEXT(AQ2))-LEN(SUBSTITUTE(FORMULATEXT(AQ2),"+","")),0),0)+IF(AQ2<>0,1,0) +IFNA(IF(AR2<>0,LEN(FORMULATEXT(AR2))-LEN(SUBSTITUTE(FORMULATEXT(AR2),"+","")),0),0)+IF(AR2<>0,1,0)

Thank you so much again.
 
Last edited by a moderator:
Upvote 0
Happy you found a solution you can work with and welcome to the forum
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question Question
How to count number of consecutive wet days in the a rainfall table?
2
Replies
19
Views
677
felixstraube
F
A
  • Question Question
Macro to count the number times the excel/powerpoint has been opened
2
Replies
14
Views
212
Jeffrey Mahoney
Jeffrey Mahoney
W
  • Question Question
Excel VBA - Check For Duplicates and Return the Count on a Large Data Set
Replies
4
Views
262
Kaper
Kaper
A
  • Solved
Calculate a sequence of results until a threshold is reached
Replies
5
Views
643
Anonymous1378
A
D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
348
Fluff
Fluff

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top