sumif for several dates (Numbers)

Hello

You might introduce mark column to control sum and use SUMIF() with condition on the mark value. Something like the following.

REPORT A1 mark A2 1 A3 2 A4 3 B1 sum [>=mark] B2 =SUMIF(DATA::C,">="&A2,DATA::B) B3 =SUMIF(DATA::C,">="&A3,DATA::B) B4 =SUMIF(DATA::C,">="&A4,DATA::B)

* Tables are built with Numbers v2.

Regards,

H

thor74hh wrote:

I'd now like to just enter three or four dates for one month and sum up expenses to that date, i.e. I need to expand the condition in my formula. (In the end I want to project my cash-flow and - subject to probably another question - add a projection for future development regarding the cash-flow.

Thus my idea to just pick key dates (first, middle and last of the month, maybe four dates) where I sum up the expenses (and income).

Hi thor 74hh,

If I understand your question correctly, you may find the SUMIFS function (not a series of SUMIF) helpful. For example:

The formula in B4 of Table 2, copied to column C:

=SUMIFS(Table 1::$C,Table 1::$A,">="&B$2,Table 1::$A,"<="&B$3)

The formula in B5 of Table 2, copied to column C:

=SUMIFS(Table 1::$D,Table 1::$A,">="&B$2,Table 1::$A,"<="&B$3)

I laid out Table 2 horizontally because you mentioned you will be doing projections, and by convention projections typically go out to the right.

Note that the logic for SUMIFS is the same as for SUMIF, except that you have multiple column-condition pairs.

SG

Hello

Here're revised tables which assign marks for dates based upon a predefined table that maps day-of-month to mark. If mark is to be determined by day of month, this should simplify record management.

DATA (excerpt) A1 date A2 2016-02-01 B1 value B2 =RANDBETWEEN(1,10)*10 C1 mark C2 =IFERROR(INDEX(MARKS::B,MATCH(DAY(A2),MARKS::A,0)), IFERROR(INDEX(MARKS::B,MATCH(DAY(A2)-DAY(EOMONTH(A2,0))-1,MARKS::A,0)),""))

Regards,

H