Jan 18 2022 08:20 AM
I'm trying to do the sum of all prices where the date in un January for each name ID that I have in an other tab.
Basically, on my first tab, I have all names ID (Bob, James, Lucas)
And my second tab have this structure. For one name ID (Bob, for example) all datas where I need to do my search will always be between the "Bob" lign and the "End Bob" line.
How to detect that "between the Bob lign and the End Bob lign, sum all prices column where the Date column is in January".
Thanks a lot for anyone who can guide me on this.
Jan 18 2022 08:31 AM
On which version/platform of Excel you are?
Jan 18 2022 08:38 AM
Jan 18 2022 10:17 AM
=SUMPRODUCT(((MONTH(INDIRECT("Feuil"&ROW(2:2)&"!C"&B1+1&":C"&B2-"1")))=B3)*INDIRECT("Feuil"&ROW(2:2)&"!D"&B1+1&":D"&B2-"1"))
This formula works in my spreadsheet as shown in the attached file. The name can be selected in A1 and in cell A7 the sum for e.g. january is calculated. The month can be dynamically chosen in cell B3.
Jan 18 2022 01:16 PM
For the source data in Sheet2
result as
could be generated with
=IFERROR(
LET(
start, XMATCH(A2,Sheet2!A:A) + 1,
end, XMATCH("end " & A2, Sheet2!A:A) - 1,
month, TEXT( INDEX( Sheet2!C:C, start ):INDEX( Sheet2!C:C, end ), "mmm"),
price, INDEX( Sheet2!D:D, start ):INDEX( Sheet2!D:D, end ),
SUM( FILTER( price, month = $B$1 ) )
), "no data" )
Please check in attached.