adding total cost from a month

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

=SUMPRODUCT(--(MONTH($C$1:$C$100)=1),--(
YEAR($C$1:$C$100)=2004),$B$1:$B$100)
I used this code from an excel master to help me with my
problem. The trouble is the display in cell G4 was #VALUE!
Let me see if I can explain better what I am trying to
achieve by explaining my spread sheet:
column A is Employee Name
B is # of Copies
C is Date
D is Cost
E is Total Spent
F is Total Remaining
G is July Total $
H is Aug Total etc...including the quarters.
Right now I have formulas that put today's date in column
c whenever the employee puts a # in column B<=IF
(B4="","",IF(C4="",NOW(),C4))>. Also, the Total Spent &
Total Remaining are set to calculate the total. My problem
is I want the July column to add only the totals in column
D (cost)that were from the month of July. Instead of
manually doing it at the end of the month i.e. =sum
(D4:D14). Can someone help me with this?
Thank you,
Andrew
 
Frank,
I'm sorry i just read your response to this and I tried
the new code =SUMPRODUCT(--(MONTH(C1:C100)=7),--(YEAR
(C1:C100)=2004),D1:D100). This also returned a display of
#VALUE! in cell G4 whether there is any data in columns B
& C or not. Is there another way?
Thank you,
Andrew
 
Hi
do you really have date values in column C. This error
seems to indicate that you don't have date values in
column C
 
Yes Frank (M/DD/YY). When I enter a value in column B,
today's date is returned in column C and G4 is staring
back at me with #VALUE!. Any ideas?
Thank you in advance.
Andrew
 
Hi
problem is the result "" which is also returned by your
formula in case the cell is empty.
 
Back
Top