Conditional sum

  • Thread starter Thread starter Hennie
  • Start date Start date
H

Hennie

I am using a workbook to capture quality costs. The worksheet for
capturing the costs contains 20 columns. The actual date of entering
the data appears against each entry.
I want to sum the costs based on certain criteria to a "summary" sheet
from where I can then create graphs. I must be able to summarize the
costs per month on the “summary” sheet.
Example: For January to December, the summary sheet must contain a
column reflecting all the costs of "Internal" defects for "Paint"
against each month in the column.
Some entries might contain empty cells - even the date.


+-------------------------------------------------------------------+
|Filename: Conditional Sum.pdf |
|Download: http://www.excelforum.com/attachment.php?postid=5130 |
+-------------------------------------------------------------------+
 
Try this in your "Summary" sheet and change Sheet1 to your "Individual
Entries" sheet:

It assumes months are in column A of "Summary" sheet and headings in Row 1
match entries in Column I & J of "Individual Entries"

=SUMPRODUCT(--(MONTH(Sheet1!$A$2:$A$20)=MONTH($A2)),--(Sheet1!$I$2:$I$20="Resin"),--(Sheet1!$J$2:$J$20="Internal")*(Sheet1!$O$2:$O$20))

HTH
 
Thing something like this might work.

You might need to enter 01-Jul-06 in A& and format cell as mmm-yy

=SUMPRODUCT(--(C1:C88>=A7)*(--(C1:C88<=A7)*(--(I1:I88="Paint")*(--(J1:J88="Internal")*(--(K1:K88))))))

VBA Noob
 
There is no need for -- and *, they both do a similar job, and there is
absolutely no need to coerce the array of numbers

Either

=SUMPRODUCT(--(C1:C88>=A7),--(C1:C88<=A7),--(I1:I88="Paint"),--(J1:J88="Inte
rnal"),K1:K88)

or

=SUMPRODUCT((C1:C88>=A7)*(C1:C88<=A7)*(I1:I88="Paint")*(J1:J88="Internal"),K
1:K88)

is sufficient.

And doesn't

--(C1:C88>=A7),--(C1:C88<=A7)

equate to

--(C1:C88=A7) ?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Back
Top