Text version of SUMIFS

J

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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
JOEE1979 said:
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
Click to expand...
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
Peter_SSs said:
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.
Click to expand...
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
bebo021999 said:
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)
Click to expand...
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
Peter_SSs said:
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
Click to expand...
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
JOEE1979 said:
Didnt work

V4 and D2 are part of a calendar
V4 is the day
D2 is the month and year
Click to expand...
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
Peter_SSs said:
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)))
Click to expand...
Hoping this would be better help

Worksheet "Current Month"
Schedule 4 Tracker - 1111.xlsx
BDEGHJKMNPQSTVWAJAKALAM
2November 2024
3SundayMondayTuesdayWednesdayThursdayFridaySaturday
4Shop27Shop28Shop29Shop30Shop31Shop1Shop2
524372462251224470255324491Time Entry
624682469252125510258224532
724812473830625700835124883
825332517832325830835824904
925492584833525870840424915Year2024
1025772599833983000842325296
1183492614837383570844325507Month11
1283888327839383760845425948
1384018363843783860846183169Day 11
14851183928439839108475832510
15853684748457842508481833711Date2024-11-03
16853984828483844008494840712
17854085258493851808505841113
18854285278496008534844714
19085588509000850415
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"

211111.xlsx
ABFGN
61272-Nov-242449OSH2-Nov-241
61282453STVL2-Nov-242
61292488SRK2-Nov-243
61302490EG2-Nov-244
613124912-Nov-245
613225292-Nov-246
613325502-Nov-247
613425942-Nov-248
613583162-Nov-249
613683252-Nov-2410
613783372-Nov-2411
613884072-Nov-2412
613984112-Nov-2413
614084472-Nov-2414
614185042-Nov-2415
614202-Nov-2416
614302-Nov-2417
614402-Nov-2418
614502-Nov-2419
614602-Nov-2420
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
You must log in or register to reply here.

Similar threads

J
  • Question Question
out of my league, need help
Replies
1
Views
89
Simadri1986
S
C
  • Question Question
SUMIF being used to compare differences between lists, not working correctly
Replies
1
Views
247
StephenCrump
S
J
  • Solved
conditional format to change color
2
Replies
10
Views
645
SanjayGMusafir
SanjayGMusafir
D
  • Question Question
I need to reference sheets with names that are dates, but cannot get formula to work!
Replies
3
Views
161
RoryA
RoryA
M
  • Solved
#DIV/0! error
Replies
3
Views
195
Micron
M

Forum statistics

Threads
1,223,577
Messages
6,173,164
Members
452,504
Latest member
frankkeith2233

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