Array inside the SUMIF problem

**AHB10** · 01-09-2013, 07:27 AM

Good day

As you can find in formula for the highlighted rows in yellow in the attached file, I am trying to sum specific cells in the column if the value of other cells are valid.

I tried to use SUMIF and/or SUMIFS but I cannot make it work. I am thinking of using array inside the SUMIF but unfortunately it not working with me either. Currently I am using the following formula and it is fine but the problem would be if I have 100 packages. The formula would be long and the mistakes would be easily happened.

=IF(D$18="Received",D$16)+IF(D$35="Received",D$33)+IF(D$52="Received",D$50)

I believe that there is a butter solution. Do you have any suggestion?

Best regards

**Kevin UK** · 01-09-2013, 08:17 AM

Hi

Sumifs will not accept array's. Look into SUMPRODUCT & SUMIFS

**Ace_XL** · 01-09-2013, 08:23 AM

Try this..

In D61

Please Login or Register  to view this content.

In D62

Please Login or Register  to view this content.

**benishiryo** · 01-09-2013, 08:30 AM

hi AHB10, welcome to the forum. i'll just share the SUMPRODUCT since Ace_XL have shared the SUMIFS:
=SUMPRODUCT(($B$18:$B$52="Status : Received / Outstanding")*(D$18:D$52="Received"),D$16:D$50)

basically, our intention is to make the "Net Amount Invoiced" in line with the "Status : Received / Outstanding". so you'll notice our range for Column B & D is intentionally different

**AHB10** · 01-09-2013, 09:04 AM

Dears,

Thank you for your contribution.

I’ve noticed Ace_XL answer and i was asking myself why until it is explained by benishiryo. It was a trick I never thought about but I am still wondering about if any rows inserted in between for one package but not in the others, is this formula going to be affected although I’ve tried and it is not. Is it possible that both criteria would not be in line with each other using these formulas??

Thanks and best regards

**AHB10** · 01-12-2013, 09:59 AM

Dears,

Could any body help on this ??

best regards

**benishiryo** · 01-12-2013, 10:10 AM

it is safe as long as you include those relevant columns that are in the formula when you insert a row. safest way is of course to insert an entire row.