Sumproduct needs refinement

[plettieri]

Hello friends.

I have considered the following formula that is working for me, however, I need to expand ther areas to add two more regions.

Working formula

=SUMPRODUCT((YEAR('Powers final '!$E$32:$E$211)=YEAR('Summary final'!D62))*'Powers final '!$N$32:$N$211)+SUMPRODUCT((YEAR('Powers final '!$E$218:$E$397)=YEAR($D62))*'Powers final '!$N$218:$N$397)

Desired formula
=SUMPRODUCT((YEAR('Powers final '!$E$32:$E$211)=YEAR('Summary final'!D62))*'Powers final '!$N$32:$N$211)+SUMPRODUCT((YEAR('Powers final '!$E$218:$E$397)=YEAR($D62))*'Powers final '!$N$218:$N$397)+SUMPRODUCT((YEAR('Powers final '!$E$500:$E$600)=YEAR($D62))*'Powers final '!$N$500:$N$600)+SUMPRODUCT((YEAR('Powers final '!$E$700:$E$800)=YEAR($D62))*'Powers final '!$N$700:$N$800)

This would work, but a but cumbersome to manage.

Since the data is aligned in alike columns, I tried using one range..such as

=SUMPRODUCT((YEAR('Powers final '!$E$32:$E$600)=YEAR('Summary final'!D62))*'Powers final '!$N$32:$N$600

....but these desired ranges are not contiguous and does not seem to work unless the range is not interrupted...or maybe they can and I have missed something...

Might there be an shorter version of this or another approach?

Thanks in advance for reading.

plettieri

 

[WillR]

how about


=SUMPRODUCT((YEAR('Powers Final '!$E$32:$E$600)=YEAR('Summary Final '!D62))*'Powers Final '!$N$32:$N$600)

 

[plettieri]

Thks WillR,

As noted in my question, I did try that formula you suggested and I get a "#value!" response.

Maybe I missed something....the question is, should that formula I tried and you suggested be affected by non contiguous ranges?

plettieri

 

[Domenic]

Assuming that the cells between the ranges are either blank or contain a text value, such as a label, maybe...

=SUM(IF(ISNUMBER('Powers final'!$E$32:$E$800),IF(YEAR('Powers final'!$E$32:$E$800)=YEAR('Summary final'!D62),'Powers final'!$N$32:$N$800)))

...confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly.

Hope this helps!

 

[Aladin Akyurek]

If you have the latest morefunc.xll add-in...

=SUMPRODUCT(--(YEAR(ARRAY.JOIN('Powers final '!$E$32:$E$211,'Powers final '!$E$218:$E$397,'Powers final '!$E$500:$E$600)=YEAR($D62)),ARRAY.JOIN('Powers final '!$N$32:$N$211,'Powers final '!$N$500:$N$600,'Powers final '!$N$700:$N$800)

Another option is to create a Year column (column O, foe example) on Powers final where you leave cells between relevant ranges empty. This allows you to invoke:

=SUMIF('Powers final '!$O$32:$O$600,YEAR($D62),'Powers final '!$N$32:$N$600)

 

[plettieri]

Mr Aladin,

Tks for your reply

I did think about the "helper column" concept and was aware of that but, I was trying think of way without that aid. The helper concept works quite well ..(and maybe the easiest to explain solution to others).

Your solution when tried resulted appears to be missing some ")" or "(".

I inserted the brackets where I thought appropriate, but maybe not so well placed....in this revised formula

=SUMPRODUCT(--(YEAR(ARRAY.JOIN('Powers final '!$E$32:$E$211,'Powers final '!$E$218:$E$397,'Powers final '!$E$500:$E$600)=YEAR($D62))),ARRAY.JOIN('Powers final '!$N$32:$N$211,'Powers final '!$N$500:$N$600,'Powers final '!$N$700:$N$800))


This formula produces the reply of #VALUE!. I do have the
morefunc.xll add-in installed.

Tks as always
plettieri

 

[Aladin Akyurek]

Mr Aladin,

Tks for your reply

I did think about the "helper column" concept and was aware of that but, I was trying think of way without that aid. The helper concept works quite well ..(and maybe the easiest to explain solution to others).

Your solution when tried resulted appears to be missing some ")" or "(".

I inserted the brackets where I thought appropriate, but maybe not so well placed....in this revised formula

=SUMPRODUCT(--(YEAR(ARRAY.JOIN('Powers final '!$E$32:$E$211,'Powers final '!$E$218:$E$397,'Powers final '!$E$500:$E$600)=YEAR($D62))),ARRAY.JOIN('Powers final '!$N$32:$N$211,'Powers final '!$N$500:$N$600,'Powers final '!$N$700:$N$800))


This formula produces the reply of #VALUE!. I do have the
morefunc.xll add-in installed.

Tks as always
plettieri

Vagaries of copying and pasting ranges from your original formula...

=SUMPRODUCT(--(YEAR(ARRAY.JOIN('Powers final '!$E$32:$E$211,'Powers final '!$E$218:$E$397,'Powers final '!$E$500:$E$600))=YEAR($D62)),ARRAY.JOIN('Powers final '!$N$32:$N$211,'Powers final '!$N$500:$N$600,'Powers final '!$N$700:$N$800))

 

[plettieri]

Hi:

Just to close this thread, there were several solutions offered that will perform the tast requested...I made a small reference correction to Aladin's solution and that works very in my application.


=SUMPRODUCT(--(YEAR(ARRAY.JOIN('Powers final'!$E$32:$E$211,'Powers final'!$E$218:$E$397,'Powers final'!$E$500:$E$600))=YEAR($D62)),ARRAY.JOIN('Powers final'!$N$32:$N$211,'Powers final'!$N$218:$N$397,'Powers final'!$N$500:$N$600))

Thanks to all

plettieri