Sumif formula

[chssundeep]

Hello,

We sold a product with 3-4 payment milestones.. To track it and check the cash flow we have prepared a sheet... The challenge here is if payment milestones are more I am forced to use multiple sumif formulas which is taking a lot of time for excel to get the data..

Could you please help me with a simple formula..

Attached sample file

 

[vletm]

chssundeep
Use Pivot-table.

 

[bosco_yip]

1] In the "Cash flow" sheet, your adding helper "column L" is not required.

Then,

2] Changed and shortened your formula in the "Cash flow" sheet C4,

From this >>

=SUMIFS(Segate!E:E,Segate!F:F,">="&'Cash flow'!B4,Segate!F:F,"<="&'Cash flow'!L4)+SUMIFS(Segate!I:I,Segate!J:J,">="&'Cash flow'!B4,Segate!J:J,"<="&'Cash flow'!L4)+SUMIFS(Segate!M:M,Segate!N:N,">="&'Cash flow'!B4,Segate!N:N,"<="&'Cash flow'!L4)

To this >>

=SUMIFS(Segate!E:M,Segate!F:N,">="&B4,Segate!F:N,"<="&EOMONTH(B4,0))

and copied down

Regards
Bosco

 

[chssundeep]

Thank you sooo much...........That's perfect solution

But 1 more challenge... I need to know Invoice raised and payment pending Amount...If payment received I will enter the date in Payment received on column...If blank, payment is pending...Could you help me with this formula please.

1] In the "Cash flow" sheet, your adding helper "column L" is not required.

Then,

2] Changed and shortened your formula in the "Cash flow" sheet C4,

From this >>

=SUMIFS(Segate!E:E,Segate!F:F,">="&'Cash flow'!B4,Segate!F:F,"<="&'Cash flow'!L4)+SUMIFS(Segate!I:I,Segate!J:J,">="&'Cash flow'!B4,Segate!J:J,"<="&'Cash flow'!L4)+SUMIFS(Segate!M:M,Segate!N:N,">="&'Cash flow'!B4,Segate!N:N,"<="&'Cash flow'!L4)

To this >>

=SUMIFS(Segate!E:M,Segate!F:N,">="&B4,Segate!F:N,"<="&EOMONTH(B4,0))

and copied down

Regards
Bosco

 

[chssundeep]

Attached the file with suggested formula and new challenge I faced.... Please help

 

[bosco_yip]

Your revised file in Post #4 is different from Post #1 with additional multiple criteria date columns,

this revised layout caused the Sumifs formula giving wrong results, so cell C9 and C10 results are not corrected.

The revised formulas solution are put in Column J to L and show as in :

1] In "Proposed Inv to be raised" J4, copied right to "Actual Inv Raised" K4 and all copied down :

=SUMPRODUCT(N(OFFSET(Segate!$E$2,ROW($A$1:$A$100),{0,5,10}))*(TEXT(N(OFFSET(Segate!F$2,ROW($A$1:$A$100),{0,5,10})),"mmyy")=TEXT($B4,"mmyy")))

2] In "Pending Amount" L4, copied down :

=SUMPRODUCT(N(OFFSET(Segate!$E$2,ROW($A$1:$A$100),{0,5,10}))*(TEXT(N(OFFSET(Segate!G$2,ROW($A$1:$A$100),{0,5,10})),"mmyy")=TEXT($B4,"mmyy"))*(N(OFFSET(Segate!I$2,ROW($A$1:$A$100),{0,5,10}))=0))

3] See attached file

Regards
Bosco

 

[chssundeep]

Your revised file in Post #4 is different from Post #1 with additional multiple criteria date columns,

this revised layout caused the Sumifs formula giving wrong results, so cell C9 and C10 results are not corrected.

The revised formulas solution are put in Column J to L and show as in :

1] In "Proposed Inv to be raised" J4, copied right to "Actual Inv Raised" K4 and all copied down :

=SUMPRODUCT(N(OFFSET(Segate!$E$2,ROW($A$1:$A$100),{0,5,10}))*(TEXT(N(OFFSET(Segate!F$2,ROW($A$1:$A$100),{0,5,10})),"mmyy")=TEXT($B4,"mmyy")))

2] In "Pending Amount" L4, copied down :

=SUMPRODUCT(N(OFFSET(Segate!$E$2,ROW($A$1:$A$100),{0,5,10}))*(TEXT(N(OFFSET(Segate!G$2,ROW($A$1:$A$100),{0,5,10})),"mmyy")=TEXT($B4,"mmyy"))*(N(OFFSET(Segate!I$2,ROW($A$1:$A$100),{0,5,10}))=0))

3] See attached file

Regards
Bosco

Thank you, Thank you so much............