Trying to create formula to calculate spend per week from a month of data.

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

SUMIF can only be used for a single criterion, so you will need to use SUMIFS, as this will take multiple criteria. The syntax is a bit different than for SUMIF - essentially:

SUMIFS(range of values to sum, criterion_1_range, criterion_1, criterion_2_range, criterion_2 ...

where you have pairs of criteria_ranges and the criteria following after the sum_range.

In your case I think you will need to do this:

=SUMIFS('SITE ORDERS'!$I$3:$I$999,'SITE ORDERS'!$B$3:$B$999,B3,'SITE ORDERS'!$K$3:$K$999,">="&$D$1,'SITE ORDERS'!$K$3:$K$999,"<"&$E$1)

I've used 3 different colours to highlight the three different criteria pairs.

Hope this helps.

Pete

Hi Pete,

That sorted it although I had to do ">=&$D$1" for the date criteria, as opposed to ">="&$D$1. But its working great, thanks a lot.

Glad to help, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete