Text version of SUMIFS

JOEE1979

Active Member
Joined
Dec 18, 2022
Messages
292
Office Version
  1. 365
Platform
  1. Windows
I have a formula

Excel Formula:
=SUMIFS((INDIRECT(AL9&"!F:F")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W6)

However, I have text in F:F, and I know SUMIFS returns a number.
How would I be able to fix this to make if give me a text and not a number?

Thanks
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I have a formula

Excel Formula:
=SUMIFS((INDIRECT(AL9&"!F:F")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W6)

However, I have text in F:F, and I know SUMIFS returns a number.
How would I be able to fix this to make if give me a text and not a number?

Thanks
Here is the same formula but without the indirect function to make it more clear

Excel Formula:
=SUMIFS(2024!F:F),(2024!G:G),(02-NOV-2024),(2024!N:N),$W6)
 
Upvote 0
With XL2BB could you give a small set of dummy sample data from the 2024 sheet columns F:N after first hiding columns H:M?
Tell us what is in $W6 of the formula worksheet and tell us what the actual result of the formula should be for that specific set of data.
 
Upvote 0
With XL2BB could you give a small set of dummy sample data from the 2024 sheet columns F:N after first hiding columns H:M?
Tell us what is in $W6 of the formula worksheet and tell us what the actual result of the formula should be for that specific set of data.
Column W6 would match column N, and if the dates match in column G, then it will give me the result in column F


Schedule 4 Tracker - 1111.xlsx
W
62
Current Month



Schedule 4 Tracker - 1111.xlsx
FGN
6127OSH2-Nov-241
6128STVL2-Nov-242
6129SRK2-Nov-243
2024
Cell Formulas
RangeFormula
G6127G6127=A6127
G6128G6128=A6127
G6129G6129=A6127
 
Upvote 0
I think it is not SUMIFS: summing value, but returning cell value, WHATEVER ITS VALUE, text or number

Code:
=LOOKUP(2,1/(INDIRECT(AL9&"!$G$1:$G$10000")=TEXT(V$4,"DD-")&TEXT($D$2,"MMM-YY"))/
(INDIRECT(AL9&"!$N$1:$N$10000")=$W6),INDIRECT(AL9&"!$F$1:$F$10000"))

Format: = LOOKUP(2,1/(COLUMN_1 = CRI_1)/(COLUMN_2 = CRI_2), RESULST_COLUMN)
 
Upvote 0
I think it is not SUMIFS: summing value, but returning cell value, WHATEVER ITS VALUE, text or number

Code:
=LOOKUP(2,1/(INDIRECT(AL9&"!$G$1:$G$10000")=TEXT(V$4,"DD-")&TEXT($D$2,"MMM-YY"))/
(INDIRECT(AL9&"!$N$1:$N$10000")=$W6),INDIRECT(AL9&"!$F$1:$F$10000"))

Format: = LOOKUP(2,1/(COLUMN_1 = CRI_1)/(COLUMN_2 = CRI_2), RESULST_COLUMN)
Did not work
 
Upvote 0
Thanks for the sample data. It helps clarify a bit but there still could be some issues as ..
  • We do not know if the dates in column A are actual dates (numbers) or text values
  • There is some confusion to me because the dates shown in column G show single-digit day values but your original formula had TEXT(V$4<,"DD-") which would give double-digit day values.
Anyway, this would be my best guess.
Like @bebo021999 I am trying to avoid whole column references and have allowed for 10,000 rows. Change the 10000 if you are likely to have more than that
Excel Formula:
=LET(d,CHOOSECOLS(INDIRECT(AL9&"!F$1:N$10000"),1,2,9),XLOOKUP(W$6&TEXT(V$4,"-D-")&TEXT($D$2,"MMM-YY"),TAKE(d,,-1)&"-"&INDEX(d,0,2),TAKE(d,,1)))

If this does not work, please provide more information about the two bullet points above.
Also provide details of exactly what is in V4 and D2
 
Upvote 0
Thanks for the sample data. It helps clarify a bit but there still could be some issues as ..
  • We do not know if the dates in column A are actual dates (numbers) or text values
  • There is some confusion to me because the dates shown in column G show single-digit day values but your original formula had TEXT(V$4<,"DD-") which would give double-digit day values.
Anyway, this would be my best guess.
Like @bebo021999 I am trying to avoid whole column references and have allowed for 10,000 rows. Change the 10000 if you are likely to have more than that
Excel Formula:
=LET(d,CHOOSECOLS(INDIRECT(AL9&"!F$1:N$10000"),1,2,9),XLOOKUP(W$6&TEXT(V$4,"-D-")&TEXT($D$2,"MMM-YY"),TAKE(d,,-1)&"-"&INDEX(d,0,2),TAKE(d,,1)))

If this does not work, please provide more information about the two bullet points above.
Also provide details of exactly what is in V4 and D2
Didnt work

V4 and D2 are part of a calendar
V4 is the day
D2 is the month and year

Schedule 4 Tracker - 1111.xlsx
V
42
Current Month
Cell Formulas
RangeFormula
V4V4=S4+1


Schedule 4 Tracker - 1111.xlsx
DEFGHIJKLMNOPQRSTUV
2November 2024
Current Month
Cell Formulas
RangeFormula
D2D2=DATE(AL9,AL11,1)
 
Upvote 0
Didnt work

V4 and D2 are part of a calendar
V4 is the day
D2 is the month and year
So you gave details about V4 and D2 that I asked for, but nothing about clarifying the two bullet points that I had, ;)

Anyway, here are my test sheets with a new formula suggestion. If this still does not work for you, what do I have wrong with my data.
Notice that in A6127 of the 2024 sheet I have a text date. This relates to one of the bullet points that I asked about.

JOEE1979.xlsm
AFGN
61272-Nov-24OSH2-Nov-241
6128STVL2-Nov-242
6129SRK2-Nov-243
2024
Cell Formulas
RangeFormula
A6127A6127=TEXT(DATE(2024,11,2),"d-mmm-yy")
G6127G6127=A6127
G6128G6128=A6127
G6129G6129=A6127


Here is my 'Current Month' sheet.
S4 just contains the number 1

JOEE1979.xlsm
DSVWAL
1
2November 2024
3
412
5
6STVL2
7
8
92024
10
1111
Current Month
Cell Formulas
RangeFormula
D2D2=DATE(AL9,AL11,1)
V4V4=S4+1
V6V6=LET(d,CHOOSECOLS(INDIRECT(AL9&"!F$1:N$10000"),1,2,9),XLOOKUP(W$6&TEXT($D$2+$V$4-1,"-D-MMM-YY"),TAKE(d,,-1)&"-"&INDEX(d,0,2),TAKE(d,,1)))
 
Upvote 0
So you gave details about V4 and D2 that I asked for, but nothing about clarifying the two bullet points that I had, ;)

Anyway, here are my test sheets with a new formula suggestion. If this still does not work for you, what do I have wrong with my data.
Notice that in A6127 of the 2024 sheet I have a text date. This relates to one of the bullet points that I asked about.

JOEE1979.xlsm
AFGN
61272-Nov-24OSH2-Nov-241
6128STVL2-Nov-242
6129SRK2-Nov-243
2024
Cell Formulas
RangeFormula
A6127A6127=TEXT(DATE(2024,11,2),"d-mmm-yy")
G6127G6127=A6127
G6128G6128=A6127
G6129G6129=A6127


Here is my 'Current Month' sheet.
S4 just contains the number 1

JOEE1979.xlsm
DSVWAL
1
2November 2024
3
412
5
6STVL2
7
8
92024
10
1111
Current Month
Cell Formulas
RangeFormula
D2D2=DATE(AL9,AL11,1)
V4V4=S4+1
V6V6=LET(d,CHOOSECOLS(INDIRECT(AL9&"!F$1:N$10000"),1,2,9),XLOOKUP(W$6&TEXT($D$2+$V$4-1,"-D-MMM-YY"),TAKE(d,,-1)&"-"&INDEX(d,0,2),TAKE(d,,1)))
Hoping this would be better help

Worksheet "Current Month"
Cell Formulas
RangeFormula
D2D2=DATE(AL9,AL11,1)
D3,V3,S3,P3,M3,J3,G3D3=D4
D4D4=$D$2-(WEEKDAY($D$2,1)-(start_day-1))-IF((WEEKDAY($D$2,1)-(start_day-1))<=0,7,0)+1
D5D5=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W5)
D6D6=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W6)
D7D7=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W7)
D8D8=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W8)
D9D9=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W9)
D10D10=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W10)
D11D11=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W11)
D12D12=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W12)
D13D13=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W13)
D14D14=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W14)
D15D15=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W15)
D16D16=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W16)
D17D17=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W17)
D18D18=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W18)
D19D19=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(D$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W19)
G4,V4,S4,P4,M4,J4G4=D4+1
G5G5=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W5)
G6G6=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W6)
G7G7=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W7)
G8G8=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W8)
G9G9=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W9)
G10G10=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W10)
G11G11=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W11)
G12G12=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W12)
G13G13=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W13)
G14G14=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W14)
G15G15=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W15)
G16G16=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W16)
G17G17=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W17)
G18G18=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W18)
G19G19=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(G$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W19)
J5J5=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W5)
J6J6=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W6)
J7J7=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W7)
J8J8=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W8)
J9J9=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W9)
J10J10=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W10)
J11J11=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W11)
J12J12=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W12)
J13J13=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W13)
J14J14=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W14)
J15J15=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W15)
J16J16=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W16)
J17J17=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W17)
J18J18=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W18)
J19J19=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(J$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W19)
M5M5=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W5)
M6M6=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W6)
M7M7=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W7)
M8M8=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W8)
M9M9=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W9)
M10M10=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W10)
M11M11=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W11)
M12M12=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W12)
M13M13=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W13)
M14M14=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W14)
M15M15=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W15)
M16M16=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W16)
M17M17=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W17)
M18M18=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W18)
M19M19=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(M$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W19)
P5P5=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W5)
P6P6=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W6)
P7P7=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W7)
P8P8=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W8)
P9P9=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W9)
P10P10=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W10)
P11P11=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W11)
P12P12=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W12)
P13P13=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W13)
P14P14=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W14)
P15P15=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W15)
P16P16=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W16)
P17P17=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W17)
P18P18=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W18)
P19P19=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(P$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W19)
S5S5=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W5)
S6S6=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W6)
S7S7=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W7)
S8S8=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W8)
S9S9=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W9)
S10S10=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W10)
S11S11=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W11)
S12S12=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W12)
S13S13=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W13)
S14S14=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W14)
S15S15=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W15)
S16S16=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W16)
S17S17=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W17)
S18S18=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W18)
S19S19=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(S$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W19)
V5V5=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W5)
V6V6=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W6)
V7V7=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W7)
V8V8=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W8)
V9V9=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W9)
V10V10=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W10)
V11V11=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W11)
V12V12=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W12)
V13V13=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W13)
V14V14=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W14)
V15V15=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W15)
V16V16=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W16)
V17V17=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W17)
V18V18=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W18)
V19V19=SUMIFS((INDIRECT(AL9&"!B:B")),(INDIRECT(AL9&"!G:G")),(CONCAT((TEXT(V$4,"DD-")),(TEXT($D$2,"MMM-YY")))),(INDIRECT(AL9&"!N:N")),$W19)
AL9AL9=YEAR(AL15)
AL11AL11=MONTH(AL15)
AL15AL15=TODAY()


Worksheet "2024"

Cell Formulas
RangeFormula
A6127A6127=A6107+1
B6127:B6146B6127=(B5527)
G6127G6127=A6127
G6128G6128=A6127
G6129G6129=A6127
G6130G6130=A6127
G6131G6131=A6127
G6132G6132=A6127
G6133G6133=A6127
G6134G6134=A6127
G6135G6135=A6127
G6136G6136=A6127
G6137G6137=A6127
G6138G6138=A6127
G6139G6139=A6127
G6140G6140=A6127
G6141G6141=A6127
G6142G6142=A6127
G6143G6143=A6127
G6144G6144=A6127
G6145G6145=A6127
G6146G6146=A6127



What I'm trying to do is extract the info from column "F" (named Garage) of worksheet "2024", and put it in the proper columns of worksheet (named Shop) "Current Month"
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

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