SUMIFS between dates and for specified column

[e5k31]

Hello guys,

I am trying to sum cell values between specified dates and for a column containing a specific label but I am getting Err502 (LO v7.0.3.1)
I have dates, beginning and ending of a month specified in Sheet “Start” (also defined cells with name i.e. JanFirst and JanLast). In Sheet “Yesterday” I have a table that is calculating sums from another 2 Sheets (Today Stat+TodayFore) for all the days in the year (All dates in the year are also named range “Yesterday_Date”), Now in Sheet “Yesterday Summary” I want to sum all values in Sheet Yesterday, between the specified dates (JanFirst and JanLast), I would also like to only sum values from a column marked with a specific label that are in both (Yesterday Summary and Yesterday sheets)

Formula:
=SUMIFS($Yesterday.B4:T369 , Yesterday_Date , “>=”&JanFirst , Yesterday_Date , “<=”&JanLast , $Yesterday.B3:T3,B2)

I am getting the Err 502

Yesterday.B4:T369 - table of values to be summed up,
Yesterday_Date - named range of all dates in the year,
JanFirst - First date of January,
JanLast - Last date of January,
Yesterday.B3:T3 - range of names of products,
B2 - The cell containing the product name to calculate the sum for

All cells as far as I can tell were formatted correctly (numbers, dates, text) to allow the logical tests to complete
I know this may be clear as muddy water but if more explanation is required I will provide it.
Pls help not sure what is the issue here.

[anon73440385]

Hello,

you seem to fundamentally ignore the following (cite from LibreOffice Help - SUMIFS)

Func_Range and Range1, Range2… must have the same size, otherwise the function returns err:502 - Invalid argument.

Your Func_Range Yesterday.B4:T369 consist of 6954 cells, hence Yesterday_Date must be also 6954 cells filled with dates (366 rows and 19 columns). In other words: Each cell in Yesterday.B4:T369 requires a corresponding cell in a range called Yesterday_Date.

Imagine SUMIFS working like a loop over all cells, starting in B4, checking condition in related cell in Range1, then moving to B5, again checking a related cell in Range1 for matching the condition in that cell and so on until finally ending in cell T369, which also requires a related cell in Range1 for which a condition can be checked.

But may be you simply misunderstood Func_Range: This contains the values to be added and in almost all cases this is a single column. In your case this could be B4:B369 (or another single column from row 4 through row 369)

Hope that helps.