Function Help

[mib1019]

Hello all,

I've got a spreadsheet organized roughly like this. Many more rows and 13 sets of columns(plus two total columns) for 2021-Q1 Weeks.
I need a formula for counting dates (they are in text format) across the rows at Date Count, and by row where dates are entered with comma separators (see below, bolded cells)

Hope you can help me here. I'm a bit under the gun to get this to my person tomorrow. :\

Thanks, MIB1019

Program	WK1 Dates	WK1 Cost	WK2 Dates	WK2 Cost	WK3 Dates	WK3 Cost	Date Count	Total Cost
Program 1	28,30,1	$1500	5,7	$1000	11,13,15	$1500	Count dates in all Dates columns (8)	Sum of dollar columns
Program 2	29,31	$1000	4,6,8	$1500	12,14,16	$1500	Count dates in all Dates columns (8)	Sum of dollar columns
Program 3	28,29,30,31,1	$2500	4,5,6,7,8	$2500	11,12,13,14,15	$2500	Count dates in all Dates columns (15)	Sum of dollar columns
TOTALS	Count 'dates' trimming '," (10)	Sum Function	Count 'dates' trimming '," (10)	Sum Function	Count 'dates' trimming '," (11)	Sum Function	Sum of date count rows (31)	Sum Total Cost

 

[namliam]

I have a dutch version but this will do the trick:

=LENGTE( 
  SUBSTITUEREN(
   SUBSTITUEREN(
    SUBSTITUEREN(
     SUBSTITUEREN( 
      SUBSTITUEREN(
       SUBSTITUEREN(
        SUBSTITUEREN(
         SUBSTITUEREN(
          SUBSTITUEREN(
           SUBSTITUEREN(A1
                  ;0;"")
                 ;1;"")
                ;2;"")
               ;3;"")
              ;4;"")
             ;5;"")
            ;6;"")
           ;7;"")
          ;8;"") 
         ;9;"")
        )+1

I think Lengte in the English versions is Len (or Length) and Substitueren is REPLACE.
Rince repeat replacing A1 for the cells you want to count.

I hope this helps you get rid of the gun

 

[pbaldy]

Simpler I think:

=LEN(F11)-LEN(SUBSTITUTE(F11,",",""))+1

F11 being the cell containing the date string in my test.

 

[mib1019]

Thanks you guys! That works per cell. Without adding columns (and hiding them) to do a sum of the counts, any ideas?

 

[Isaac]

Thanks you guys! That works per cell. Without adding columns (and hiding them) to do a sum of the counts, any ideas?

What do you mean by that? What Paul gave you should be all you need. Sum it where desired. If you have a further question can you upload a spreadsheet and place the desired result in the cell where you have the question:?

 

[pbaldy]

Simplest is probably this type of thing:

=(LEN(F11)-LEN(SUBSTITUTE(F11,",",""))+1) + (LEN(F12)-LEN(SUBSTITUTE(F12,",",""))+1) + (LEN(F13)-LEN(SUBSTITUTE(F13,",",""))+1)

 

[namliam]

Simpler I think:

=LEN(F11)-LEN(SUBSTITUTE(F11,",",""))+1

F11 being the cell containing the date string in my test.

Is the reverse and sure looks simpeler

 

[namliam]

Thanks you guys! That works per cell. Without adding columns (and hiding them) to do a sum of the counts, any ideas?

The formula, ok, takes some creativity, but this seriously?

 

[mib1019]

Thank you all for the help. Nevermind my second question. Your formula(s) are all that I need.

You all rock!
MIB1019

 

[pbaldy]

Glad you got it working.