Separating invoices by invoice date then summing by quarter

[neroamdrid]

Hi folks...

On the 1st sheet of my document I have a proposal/invoice tracker in which
the last column for each row is N and contains the date that I create the
invoice, data that I enter manually.

On sheet 2 I have set up a calculator that does all the math for me to
produce my final tax payment. I have copied the table 4 times and formatted
by color so that each represents one quarter, 3 months.

There are only 2 pieces of information that I need to enter in order to get
the final calculation:

1) The amount I invoiced - see below
2) my expenses - this is something I track elsewhere and input manually so I
don't need help on this one.

My problem is how to create a conditional statement to grab the amounts from
sheet one and place them into the appropriate cell in sheet 2.

The logic I need to create is as follows...

For cell B5 on sheet 2 (Q1) I want to include all values from Column E on
sheet 1 where the date in Column N of that row falls between Jan 1 - Mar 31

For cell E5 on sheet 2 (Q1) I want to include all values from Column E on
sheet 1 where the date in Column N of that row falls between Apr 1 - Jun 30

For cell H5 on sheet 2 (Q1) I want to include all values from Column E on
sheet 1 where the date in Column N of that row falls between Jul 1 - Sep 30

For cell K5 on sheet 2 (Q1) I want to include all values from Column E on
sheet 1 where the date in Column N of that row falls between Oct 1 - Dec 31

How can I go about doing this?

 

[Toppers]

Assuming ONE calendar year, then

for Jan-Mar: (B5)

=SUMPRODUCT(--(Sheet1!N1:N1000<>""),--(MONTH(Sheet1!N1:N1000)>=1),--(MONTH(Sheet1!N1:N1000)<=3),Sheet1!E1:E1000)

for Apr-Jun: (E5)

=SUMPRODUCT(--(Sheet1!N1:N1000<>""),--(MONTH(Sheet1!N1:N1000)>=4),--(MONTH(Sheet1!N1:N1000)<=6),Sheet1!E1:E1000)

repeat for other quarters


For SUMPRODUCT, ranges cannot be a whole column: changes ranges to suit your
need.


HTH

 

[Roger Govier]

Hi

One way
Cell B5
=SUMPRODUCT((Sheet1!$N$2:$N$1000,<=Date(2006,3,31))*
(Sheet1!$E$2:$E$1000))

Cell E5
=SUMPRODUCT((Sheet1!$N$2:$N$1000,>Date(2006,3,31))*
(Sheet1!$N$2:$N$1000,<=Date(2006,6,30))*
(Sheet1!$E$2:$E$1000))

Repeat above for cells H5 and K5 and adjust Dates accordingly
Change ranges to suit, but note that you cannot use whole column ranges
with Sumproduct, and, ranges must be of equal size.

If you were able to insert a new row 1 on your Sheet 2 and put the
quarter end date in cells B1, E1 etc. then you could amend the second
formula to
=SUMPRODUCT((Sheet1!$N$2:$N$1000,>B$1)*
(Sheet1!$N$2:$N$1000,<=E$1)*
(Sheet1!$E$2:$E$1000))
and just copy to H5 and K5 without amendment.

Also note, if your sheet names have spaces, e.g. Sheet 1, then you need
to wrap the sheet names in single quotes e.g.
'Sheet 1'!$N$2:$N$1000