Conditional sum
 

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 |
+-------------------------------------------------------------------+

--
Hennie
------------------------------------------------------------------------
Hennie's Profile: http://www.excelforum.com/member.php...nfo&userid=399
View this thread: http://www.excelforum.com/showthread...hreadid=566419

Conditional sum
 

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

"Hennie" wrote:


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 |
+-------------------------------------------------------------------+

--
Hennie
------------------------------------------------------------------------
Hennie's Profile: http://www.excelforum.com/member.php...nfo&userid=399
View this thread: http://www.excelforum.com/showthread...hreadid=566419

Conditional sum
 

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


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=566419

Conditional sum
 

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="Pai nt")*(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)

"VBA Noob" wrote in
message ...

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="I
nternal")*(--(K1:K88))))))

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile:
http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=566419