Calculate the totals for each supplier between two dates

[ettamucci]

Hi,

I am not sure if this is possible in its current format, however I need to find all occurrences of each supplier over a given period (i.e start date & end date) and add their corresponding invoice totals to get the total spend for that period.

Attached is a sample of the data set.

I have tried using SUMPRODUCT but have only managed to get it to calculate how many times a given supplier is shown in the period but not sum their invoice totals.

i.e =SUMPRODUCT((A2:A15>=B18)*(A2:A15<=B19)*(C2:X15=A23)) --> when i have tried adding arguments to this formula to add invoice totals, I get #value or #N/A.

Any assistance would be most appreciated.

[oeldere]

g23 =SUMPRODUCT(($A$2:$A$15>=$B18)*($A$2:$A$15<=$B19)*(E$2:E$15=$A23)*(G$2:G$15)) and copy to the right in column K, O etc

after that drag the formula (for the whole range) down.

b23 =SUM(E23:X23) and drag the formula down.

See the atttached file.

[Nivvi]

Hi,

You can try sumif option

Please Login or Register  to view this content.

If you remove dates it will sum whole range and dates can work as range limiters. At the moment it will work for only one block of invoices but you could expand it more. What i would suggest is duplicating dates and keeping everything its separate row.

Please Login or Register  to view this content.

[ettamucci]

Thank you both for your great suggestions.

I slightly modified oeldere's formula to check all corresponding columns together and it worked perfectly.

thanks again..

[oeldere]

Please add the modified solution (including the file) on the forum, so other forummembers can also learn from it.

[ettamucci]

Yes, good point.

Here is the modified formula and file.
=SUMPRODUCT(($A$2:$A$15>=$B$18)*($A$2:$A$15<=$B$19)*(($E$2:$E$15=$A23)*($G$2:$G$15)+($I$2:$I$15=$A23)*($K$2:$K$15)+($M$2:$M$15=$A23)*($O$2:$O$15)+($Q$2:$Q$15=$A23)*($S$2:$S$15)+($U$2:$U$15=$A23)*($W$2:$W$15)))

It is essentially Oeldere's formula but with the additional step taken out of having to sum the totals under each column.

Thank you again guys.

[oeldere]

Thanks for marking the question solved.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.