Help needed in one of excel problem.

[Gaurav Verma]

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.

 

[Kaper]

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.

 

[MARK858]

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
	A	B	C	D	E	F	G	H
1	Team member	Sum	01/03/2024	02/03/2024	03/03/2024	04/03/2024	05/03/2024	06/03/2024
2	A	12	18	0	0	17	0	22
3			4	0	0	3	0	5
4	B	9	13	0	0	12	22	0
5			2	0	0	3	4	0
6	C	10	56	0	13	0	1	0
7			6	0	3	0	1	0
Sheet3

Cell Formulas
Range	Formula
B2,B6,B4	B2	=SUM(C3:$H3)
C2	C2	=12+1+2+3
F2	F2	=6+5+6
H2	H2	=5+2+3+5+7
C3:H3,C7:H7,C5:H5	C3	=IFNA(IF(C2<>0,LEN(FORMULATEXT(C2))-LEN(SUBSTITUTE(FORMULATEXT(C2),"+","")),0),0)+IF(C2<>0,1,0)
C4	C4	=7+6
F4	F4	=5+2+5
G4	G4	=6+3+5+8
C6	C6	=22+8+12+7+3+4
E6	E6	=5+3+5

 

[Gaurav Verma]

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
	A	B	C	D	E	F	G	H
1	Team member	Sum	01/03/2024	02/03/2024	03/03/2024	04/03/2024	05/03/2024	06/03/2024
2	A	12	18	0	0	17	0	22
3			4	0	0	3	0	5
4	B	9	13	0	0	12	22	0
5			2	0	0	3	4	0
6	C	10	56	0	13	0	1	0
7			6	0	3	0	1	0
Sheet3

Cell Formulas
Range	Formula
B2,B6,B4	B2	=SUM(C3:$H3)
C2	C2	=12+1+2+3
F2	F2	=6+5+6
H2	H2	=5+2+3+5+7
C3:H3,C7:H7,C5:H5	C3	=IFNA(IF(C2<>0,LEN(FORMULATEXT(C2))-LEN(SUBSTITUTE(FORMULATEXT(C2),"+","")),0),0)+IF(C2<>0,1,0)
C4	C4	=7+6
F4	F4	=5+2+5
G4	G4	=6+3+5+8
C6	C6	=22+8+12+7+3+4
E6	E6	=5+3+5

[/RANGE

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.

 

[MARK858]

You could do it with a macro if you were doing it in Excel so

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Team member	Sum	01/03/2024	02/03/2024	03/03/2024	04/03/2024	05/03/2024	06/03/2024	07/03/2024	08/03/2024	09/03/2024	10/03/2024
2	A		18	0	0	17	0	22
3	B		13	0	0	12	22	0
4	C		56	0	13	0	1	0
Sheet2

Cell Formulas
Range	Formula
F2	F2	=6+5+6
H2	H2	=5+2+3+5+7
F3	F3	=5+2+5
G3	G3	=6+3+5+8
C2	C2	=12+1+2+3
C3	C3	=7+6
C4	C4	=22+8+12+7+3+4
E4	E4	=5+3+5

would turn into

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Team member	Sum	01/03/2024	02/03/2024	03/03/2024	04/03/2024	05/03/2024	06/03/2024	07/03/2024	08/03/2024	09/03/2024	10/03/2024
2	A	12	18	0	0	17	0	22
3			4	0	0	3	0	5
4	B	9	13	0	0	12	22	0
5			2	0	0	3	4	0
6	C	10	56	0	13	0	1	0
7			6	0	3	0	1	0
Sheet2

Cell Formulas
Range	Formula
B2,B6,B4	B2	=SUM(C3:$H3)
C2	C2	=12+1+2+3
F2	F2	=6+5+6
H2	H2	=5+2+3+5+7
C3:H3,C7:H7,C5:H5	C3	=IFNA(IF(C2<>0,LEN(FORMULATEXT(C2))-LEN(SUBSTITUTE(FORMULATEXT(C2),"+","")),0),0)+IF(C2<>0,1,0)
C4	C4	=7+6
F4	F4	=5+2+5
G4	G4	=6+3+5+8
C6	C6	=22+8+12+7+3+4
E6	E6	=5+3+5

with this macro

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.

I need to find something which is able to count + signs for a range of data

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

 

[Gaurav Verma]

with this macro

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.

 

[MARK858]

Happy you found a solution you can work with and welcome to the forum