Formula Help

[hipkey]

I am having a problem writing a formula to calculate the information I
need in report. Is it possible to write a formula that will
automatically fill in the pink table based on the information contained
in the 3 columns? I manually plugged the numbers in the pink cells but
am wondering if there is a way to automate this.

Thank you for any help.

Steve


+-------------------------------------------------------------------+
|Filename: Formula.pdf |
|Download: http://www.excelforum.com/attachment.php?postid=4737 |
+-------------------------------------------------------------------+

 

[Guest]

link says invalid attachment

 

[Guest]

I get an 'invalid attachement' message when I tried to look at what you are
referring to (thus we have no idea the significance of the pink area.)

 

[Sandy Mann]

Try following the link for * View this thread: * and then using the link in
excelforum


--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

Try following the link for * View this thread: * and then using the link in
excelforum

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[hipkey]

I am having a problem writing a formula to calculate the information
need in report. Is it possible to write a formula that wil
automatically fill in the pink table based on the information containe
in the 3 columns? I manually plugged the numbers in the pink cells bu
am wondering if there is a way to automate this.

Thank you for any help.

Stev

+-------------------------------------------------------------------
|Filename: Excel.jpg
|Download: http://www.excelforum.com/attachment.php?postid=4738
+-------------------------------------------------------------------

 

[Sandy Mann]

Reconstructing you initial data table in A1:C6 (you don't have any cell
references in your attachment) and the Results table in D9: J11 with the
Projects in D911

$0 - $49.99 # formula:
=SUMPRODUCT(($C$2:$C$6>0)*($C$2:$C$6<50)*($B$2:$B$6=D10))
Total formula:
=SUMPRODUCT(($C$2:$C$6>0)*($C$2:$C$6<50)*($B$2:$B$6=D10)*($C$2:$C$6))

$50 - $99.99 Formula:
=SUMPRODUCT(($C$2:$C$6>=50)*($C$2:$C$6<100)*($B$2:$B$6=D10))
Total formula:
=SUMPRODUCT(($C$2:$C$6>=50)*($C$2:$C$6<100)*($B$2:$B$6=D10)*($C$2:$C$6))

$100 formula:

=SUMPRODUCT(($C$2:$C$6>=100)*($B$2:$B$6=D10))
Total formula:
=SUMPRODUCT(($B$2:$B$6=D10)*($C$2:$C$6>=100)*($C$2:$C$6))

and cop down for Project ITB


--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk