Sum based on date range only positive numbers in a range, like A1:B10 possible?

[MaxG1]

I have budget spreadsheet with columns A:H, with
$Budget.A=date,
$Budget.B=description,
$Budget.C to H = columns of positive and negative numbers
A4 = January

I can SUMIF positve|negative number over one column
=SUMIF($Budget.C$3:$Budget.C$700,">=0")

I can SUMIF positve|negative number over more then one column
=SUMIF($Budget.C$3:$Budget.H$700,">=0")

I can SUMIFS the numbers in one column (say C based on date, based on a date range

=SUMIFS(
  $Budget.C$3:$Budget.C$700,
  $Budget.A$3:$Budget.A$700, ">="&A4,
  $Budget.A$3:$Budget.A$700, "<="&EOMONTH(A4,0))

… but I cannot SUMIFS these numbers over column C to H, based on a date range

=SUMIFS(
  $Budget.C$3:$Budget.H$700,
  $Budget.A$3:$Budget.A$700, ">="&A4,
  $Budget.A$3:$Budget.A$700, "<="&EOMONTH(A4,0))

The above will throw an Err:502

In effect, I cannot use the SUMIF in the SUMIFS.

My final goal is (but doesn’t work)

=SUMIFS(
  SUMIF($Budget.C$3:$Budget.H$700,">=0"),
  $Budget.A$3:$Budget.A$700, ">="&A4,
  $Budget.A$3:$Budget.A$700, "<="&EOMONTH(A4,0))

What am I missing?
Any hints appreciated.

[JohnSUN]

Let’s build this formula step by step. We need to consider three conditions - the value in column A is greater than a certain date, the value in column A is less than a certain date, and the value in the range C3:H700 is greater than zero.

The first two conditions can be written using the IF(AND(...)) functions. But this is too cumbersome. We can use the fact that the comparison operation gives 0 or 1, and then a formula like

=($Budget.$A$3:$A$700>EOMONTH(A4;-1))*($Budget.$A$3:$A$700<=EOMONTH(A4;0))

will give us a long vector in which the necessary rows will have 1, and all the rest will have 0. (I also use EOMONTH() for the beginning of the month - what if the A4 is not the first day, but some other date?)

The third condition - “I’m only interested in positive values” - can be written using IF()

{=IF($Budget.$C$3:$H$700>0;$Budget.$C$3:$H$700;0)}

This will give us a matrix of the same size as the original range, but all negative values ​​will be replaced by 0.
Now all that remains is to multiply the first vector and this matrix:

=SUMPRODUCT(($Budget.$A$3:$A$700>EOMONTH(A4;-1))*($Budget.$A$3:$A$700<=EOMONTH(A4;0))*IF($Budget.$C$3:$H$700>0;$Budget.$C$3:$H$700;0))

[MaxG1]

Wow, what an awesome reply! Thank you very much.
I did a cut an paste of the formula provided, and this works spot on
Nicely explained to! I really appreciate it.

[mikekaganski]

@JohnSUN

=($Budget.$A$3:$A$700>EOMONTH(A4;-1))*($Budget.$A$3:$A$700<=EOMONTH(A4;0))

… (I also use EOMONTH() for the beginning of the month - what if the A4 is not the first day, but some other date?)

Note that $Budget.$A$3:$A$700>EOMONTH(A4;-1) is also TRUE for datetimes with non-midnight time parts. Possibly could be improved with $Budget.$A$3:$A$700>=EOMONTH(A4;-1)+1.

[PKG]

A slightly different formula that also leads to the result.

=SUMPRODUCT((EOMONTH($Budget.$A$3:$A$700,0)=EOMONTH(A4,0))*($Budget.$C$3:$H$700>0)*($Budget.$C$3:$H$700))

The input of this formula must also be terminated with Ctrl+Shift+Enter.