[CSE] SUM Array Formula with if condition


F

Forgone

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.
 
Ad

Advertisements

F

Forgone

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top