Как создать "динамический" диапазон условия суммирования для формулы?

[kievlyanin]

Задача слудующая - есть продукты имеющие свой маркетинговый год - у одного он начинкается в мае у другого в августе. Есть таблица где числами от 1 до 12 расписано какой месяц какой номер в маркетинговом году имеет у каждого продукта (А1:М10 в примере)

И есть таблица продаж где обозначены месяца и года продаж по каждому продукту(А13:Y20 в примере)

Нужна формула которая сможет просуммировать продажи по маркетинговому году каждого продукта - продажи 1 полугодия маркетингогвого года и продажи 2 пг маркетингового года...

попытался сделать так:

=СУММПРОИЗВ((ГПР(B16:Y16;$A$3:$M$10 ;ПОИСКПОЗ($A26;$A$3:$A$11;0);ЛОЖЬ)< =6)*$B18:$Y18)

(в ячейке В26 и С26)

не работает .. ГПР видимо не подходит ... как по другому?

[kievlyanin]

так тоже не работает:

=СУММПРОИЗВ(((ИНДЕКС($A$3:$M$10;ПОИ СКПОЗ($A26;$A$3:$A$10;0);ПОИСКПОЗ($ B$16:$Y$16;$A$3:$M$3;0)))<=6)*$B18: $Y18)



хотя конструкция:

=(((ИНДЕКС($A$3:$M$10;ПОИСКПОЗ($A26 ;$A$3:$A$10;0);ПОИСКПОЗ($B$16:$Y$16 ;$A$3:$M$3;0)))<=6)*$B18:$Y18)

сама по себе работает - если протянуть по строке...

[Serge 007]

Что такое "маркетинговый год"?

Для первого полугодия:

=СУММПРОИЗВ(($A25=$A$17:$A$21)*(МЕСЯЦ($B$16:$Y$16)<7)*(ГОД($B$16:$Y$16)=2011)*$B$17:$Y$21)

[kievlyanin]

календарный год - от января до декабря ... у продукта есть маркетинговый год от начала производства до начала производства .. т.е. год к году сравнивается не по календарным годам а по маркетинговым - это может быть от августа до августа от ноября до ноября и т.д. ... размерность маркетингового года я высталяю в первой табличке - там где единичка это начало года где 12 это его конец... но табличка имеет только 12 столбцов .. вот я пытался своей формулой брать реальный диапазон суммирования с реальным диапазоном условий суммирования и "на лету" изменять значения в диапазоне условий беря их из первой таблички...

смысл моей формулы - заменить названия месяцев на номера месяца по маркетинговому году каждого продукта и затем просуммировать отдельно значения месяцев меньше 7 и больше 6.

вот эта конструкция:

=(((ИНДЕКС($A$3:$M$10;ПОИСКПОЗ($A26 ;$A$3:$A$10;0);ПОИСКПОЗ($B$16:$Y$16 ;$A$3:$M$3;0)))<=6)*$B18:$Y18)

работает когда ее тянеш по строке.

но как только делаеш СУММ или еще что-то + формула массива ессно, то она почему -то суммирует все подряд безотносительно прописанных условий.

[kievlyanin]

а это:

=СУММПРОИЗВ(($A25=$A$17:$A$21)*(МЕС ЯЦ($B$16:$Y$16)<7)*(ГОД($B$16:$Y$16 )=2011)*$B$17:$Y$21)

просуммирует календарные полугодия продуктов.

[DV68]

Так?:

=СУММПРОИЗВ(СУММЕСЛИ(B$15:Y$15;ИНДЕКС(B$6:G$10;
ПОИСКПОЗ(A25;A$6:A$10;0);0);ИНДЕКС(B$17:Y$21;ПОИСКПОЗ(A25;A$17:A$21;0);0)))

[kievlyanin]

разрисовал маркетинговые года и полугодия .. первое - более светлое - второе более темное ..

в последней, нижней табличке через СУММ прописал что именно должно просуммироваться.

на 2 календарных года получается до 3-х маркетинговых годов (неполных ессно)


вот может это прояснит ситуацию )

[kievlyanin]

Сообщение от DV68

Так?:

=СУММПРОИЗВ(СУММЕСЛИ(B$15:Y$15;ИНДЕКС(B$6:G$10;
ПОИСКПОЗ(A25;A$6:A$10;0);0);ИНДЕКС(B$17:Y$21;ПОИСКПОЗ(A25;A$17:A$21;0);0)))

не .. тут что-то вообще не то происходит .. кажись опять маркетинговые с календарными годами вносят путанницу в ситуацию )

[DV68]

Тогда так:

=СУММПРОИЗВ(СУММЕСЛИ($B$15:$Y$15;ОСТАТ(ПОИСКПОЗ(1;ИНДЕКС(B$6:M$10;ПОИСКПОЗ(A24;A$6:A$10;););)
+{-1:0:1:2:3:4};12)+1;ИНДЕКС($B$17:$Y$21;ПОИСКПОЗ($A24;$A$17:$A$21;0);0)))

[kievlyanin]

Сообщение от DV68

Тогда так:

=СУММПРОИЗВ(СУММЕСЛИ($B$15:$Y$15;ОСТАТ(ПОИСКПОЗ(1;ИНДЕКС(B$6:M$10;ПОИСКПОЗ(A24;A$6:A$10;););)
+{-1:0:1:2:3:4};12)+1;ИНДЕКС($B$17:$Y$21;ПОИСКПОЗ($A24;$A$17:$A$21;0);0)))

хм ... работает ..

а можно ликбез - что это такое:

+{-1:0:1:2:3:4};12)+1 ??