SUM based on two conditions in an array with varying lengths

[jd354]

Hi, I am new to forum and just realzied I had posted my question in another post, so I am posting a new thread. Is there anyway to delete my posts that are in the other one?

Here is my issue (example attached). I am trying to sum multiple data based on multple conditions. In my example work book, I need the output to look at the month in cell N5 and then return the sum of all the data from the array that has the same code as in column K.

I have been able to get a function that gives me the first number that meets the criteria, but not the sum.

Your help or guidance would be great!

[Moo the Dog]

Try this:

=SUMPRODUCT(($E$5:$H$5=$N$5)*($C$7:$C$15=K7)*($E$7:$H$15))

- Moo

* Note: You could also use SUMIFS to do this same calculation, however, I am working on a computer that uses Excel 2004 Mac, and SUMIFS isn't available in this version.

[Ace_XL]

Alternatively, try this array formula in N7

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter and not just Enter

Copy down

[jd354]

Thank you, for some reason this works perfect in my example, but when I put it into my acutal spreadsheet I get an #N/A or #Value!.

The array I am selecting has other numbers that do not have codes, will this matter?

[Moo the Dog]

Other numbers without codes shouldn't matter. The formulas are specifically looking for rows with the specific code, and the column with the specific date.

If you are getting errors, perhaps you could upload your actual sheet (remove any confidential information first) so we can try to find the problem.

- Moo

[jd354]

Ok here is my full sheet. Trying to get the formula to work on the "Monthly Report Outputs" tab.

Any thougths?

[Moo the Dog]

Sorry about the delay... was away from my computer for a while.

You're going to have to go with the array formula, proposed by Ace_XL, modified like this (put this in E13 on your Output sheet):

Please Login or Register  to view this content.

That can be pasted to the 3 rows below it.

If you're not familiar with Array Formulas, do this:
1. Copy formula above
2. Select cell E13 on the 'Output' sheet and delete everything in the cell
3. With cell E13 still selected, click in the formula bar and paste the formula above (the one you just copied)
4. Hold down the Ctrl and Shift keys and hit Enter

You will then be able to copy that cell, and paste it down in the other rows below.

- Moo