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

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

Hi,

I have done some basic formulas before, but I'm really struggling to get this one to work.

I have a sheet with a list of job numbers, cost values and dates.
I have a separate sheet in the same workbook to calculate the spend of each job number. I have calculated the total monthly spend with the formula below.
=SUMIF('SITE ORDERS'!\$B\$3:\$B\$999, B3, 'SITE ORDERS'!\$I\$3:\$I\$999)
=SUMIF(Range, Criteria, Sum range)
=SUMIF(Range of job numbers, Job number, range of order values)

I have gotten so far with the formula to work out the value per week

=SUMIF('SITE ORDERS'!\$I\$3:\$I\$999,B3,'SITE ORDERS'!\$B\$3:\$B\$999,'SITE ORDERS'!\$K\$3:\$K\$999,">="&D1,"<"&E1)
=SUMIF('range of order values, job number, range of job numbers, range of dates, equal or greater than 1st of month, less than 8th of month.

Any input would be appreciated...

Bosy  Register To Reply

2. ## Re: 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.  Register To Reply

3. ## Re: Trying to create formula to calculate spend per week from a month of data.

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  Register To Reply

4. ## Re: Trying to create formula to calculate spend per week from a month of data.

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.  Register To Reply

5. ## Re: Trying to create formula to calculate spend per week from a month of data.

Glad to help, and thanks for the rep.

Pete  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 