sumifs with multiple conditions

In below table i need to sum invoice amount if condition 1) : salesman code is 10657,
condition 2) : voucher no contain text 'pdcrv' or 'CRV',
most important condition 3) : if receipt date is within 10 days of invoice date

Please Login or Register  to view this content.

I am sure will get solution here.

To use SUMIFS you would need to add one more column to your data, a column with the formula:

=E2-A2

Then this SUMIFS would work:

=SUMIFS($G$2:$G$5, $D$2:$D$5, 10657, $F$2:$F$5, "<>*CRV*", $H$2:$H$5, "<=10")

Just an observation...

Originally Posted by cpg_123

condition 2) : voucher no contain text 'pdcrv' or 'CRV'

I think everyone is misinterpreting that phrase.

I don't think it means: "voucher does not contain text 'pdcrv' or 'CRV' "

If you look at the posted sample table, "voucher no" is one of the column headers.

So, my interpretation is: "if the voucher number range contains the text 'pdcrv' or 'CRV' "

Also, I think this should be relatively easy with a SUMPRODUCT function.

Further clarification from the OP including telling us what result they expect would help us help them!

My formula excludes any text string with CRV in it, which was the second exclusions criteria. You don't need to exclude pdcrv because all*crv* is already excluded, so that takes out pdcrv, too.

Originally Posted by JBeaucaire

My formula excludes any text string with CRV in it, which was the second exclusions criteria. You don't need to exclude pdcrv because all*crv* is already excluded, so that takes out pdcrv, too.

Aha, that makes sense. Sorry about my misunderstanding.

Originally Posted by Tony Valko

So, my interpretation is: "if the voucher number range contains the text 'pdcrv' or 'CRV' "

I think you're right, Tony. Good observation.

Originally Posted by JBeaucaire

To use SUMIFS you would need to add one more column to your data, a column with the formula:

=E2-A2

Then this SUMIFS would work:

=SUMIFS($G$2:$G$5, $D$2:$D$5, 10657, $F$2:$F$5, "<>*CRV*", $H$2:$H$5, "<=10")

If the goal is to have only the rows with PDCRV or CRV included in the SUM, then this alteration:

=SUMIFS($G$2:$G$5, $D$2:$D$5, 10657, $F$2:$F$5, "=*CRV*", $H$2:$H$5, "<=10")