I ended up doing the....
=TRUNC(SUM((ccb=$B7)*(ccd=$C7)*(cce=$D7)*('Workings'!AF$7:AF$345)) _
+SUM((ccb=$B7)*(ccd=$C7)*(cce=$D7)*('Workings'!AG$7:AG$345)),2)
When I 2 columns I had to sum up and
=TRUNC(SUM((ccb=$B7)*(ccd=$C7)*(cce=$D7)*('Workings'!AF$7:AF$345)) _
+SUM((ccb=$B7)*(ccd=$C7)*(cce=$D7)*('Workings'!AG$7:AG$345)) _
+SUM((ccb=$B7)*(ccd=$C7)*(cce=$D7)*('Workings'!AH$7:AH$345)),2)
When I had 3 columns to sum up.... I just changed the column numbers
and it worked fine.
Forgone <westaus...@gmail.com> wrote:
> I have a huge workbook that I run a series of various CSE Array Sum
> formulas based on various conditions.
> What I'm trying to figure out is how to modify the Sum formula to
> include an IF.
>
> This is the scenario...
>
> Criteria in worksheet B is CCB, CCC, CCD and the headings are the
> month number (EG: July = 7)
> The worksheet that contains all the data has CCB, CCC, CCD and as it
> is doing a UDF to cashflow payments for the entire year by fortnight.
> The values I'm trying to sum start in Column AF and ends in Column BE
> with the actual date of payment as the heading.
>
> The formula I had last year is below and what would happen is that I
> would have to manually add the extra formula if there was more than
> one pay in a specific month. In some months there were 3 payments and
> I'd have three lots of the formula. This formula is summing up 2
> periods.
>
> =TRUNC(SUM((ccb=$B7)*(ccd=$C7)*(cce=$D7)*('Workings'!AF$7:AF$345))
> +SUM((ccb=$B7)*(ccd=$C7)*(cce=$D7)*('Workings'!AG$7:AG$345)),2)
>
> What I am trying to figure out, is if its possible to modify this to
> be something like........
>
> =SUM((con1=x)*(con2=y)*(con3=z)*(month(pay_header_range)=monthiwanttosum)*(entiresumrange))
>
> I'm getting the feeling that it'll be something like Index.... maybe
> doing it the way I had before could be a much easier way to do it.
>
> Any suggestions, ideas would be sincerely appreciated.
|
Similar Threads
