Welcome
I just wondering if it is possible to change array formula as below on normal formula sumproduct.
=SUM(($A$3:$A$11=$F$17)*IF(G17<>"All";$B$3:$B$11=G17;1)*($C$1:$Z$1=$H$17)*CHOOSE($K$17;$C$2:$Z$2=$I$17;$C$2:$Z$2<=$I$17;$C$2:$Z$2>$I$17)*($C$3:$Z$11)) (J19 - 5914 value as below)
Above formula refers to set of data. Using some criteria I can count value. Text start is in A1. Thanks in advance!
<colgroup><col span="9"><col><col span="16"></colgroup><tbody>
</tbody>
I just wondering if it is possible to change array formula as below on normal formula sumproduct.
=SUM(($A$3:$A$11=$F$17)*IF(G17<>"All";$B$3:$B$11=G17;1)*($C$1:$Z$1=$H$17)*CHOOSE($K$17;$C$2:$Z$2=$I$17;$C$2:$Z$2<=$I$17;$C$2:$Z$2>$I$17)*($C$3:$Z$11)) (J19 - 5914 value as below)
Above formula refers to set of data. Using some criteria I can count value. Text start is in A1. Thanks in advance!
Start | Act | Act | Act | Act | Act | Act | Act | Act | Act | Act | Act | Act | Out | Out | Out | Out | Out | Out | Out | Out | Out | Out | Out | Out | |
Dep | CC | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
A | 1200 | 292 | 993 | 716 | 260 | 995 | 997 | 77 | 726 | 960 | 242 | 496 | 840 | 86 | 316 | 794 | 143 | 425 | 985 | 201 | 335 | 329 | 1 | 345 | 135 |
A | 1200 | 330 | 727 | 59 | 760 | 53 | 45 | 920 | 399 | 208 | 429 | 394 | 342 | 435 | 567 | 578 | 688 | 154 | 49 | 662 | 240 | 28 | 264 | 352 | 486 |
A | 1000 | 746 | 668 | 937 | 924 | 489 | 79 | 538 | 990 | 75 | 707 | 321 | 614 | 609 | 593 | 207 | 380 | 196 | 398 | 513 | 58 | 233 | 71 | 980 | 796 |
A | 1000 | 19 | 858 | 895 | 536 | 624 | 232 | 200 | 225 | 946 | 110 | 640 | 495 | 703 | 48 | 786 | 84 | 437 | 660 | 320 | 155 | 516 | 392 | 156 | 33 |
B | 1200 | 651 | 534 | 956 | 170 | 597 | 406 | 578 | 908 | 745 | 821 | 363 | 828 | 415 | 653 | 876 | 547 | 800 | 560 | 227 | 123 | 75 | 972 | 792 | 999 |
B | 1000 | 284 | 341 | 898 | 239 | 422 | 599 | 446 | 243 | 22 | 326 | 741 | 596 | 966 | 828 | 523 | 581 | 742 | 761 | 422 | 332 | 763 | 710 | 755 | 147 |
B | 1000 | 217 | 584 | 381 | 346 | 2 | 229 | 99 | 615 | 359 | 161 | 245 | 924 | 944 | 358 | 3 | 122 | 511 | 840 | 735 | 512 | 632 | 605 | 494 | 286 |
C | 1200 | 973 | 697 | 799 | 898 | 414 | 233 | 232 | 574 | 333 | 677 | 908 | 814 | 708 | 386 | 107 | 394 | 4 | 872 | 897 | 439 | 920 | 700 | 991 | 125 |
C | 1000 | 213 | 320 | 370 | 512 | 364 | 43 | 823 | 684 | 472 | 772 | 746 | 516 | 413 | 353 | 149 | 640 | 306 | 476 | 271 | 271 | 138 | 16 | 308 | 173 |
Monthly | 1 | ||||||||||||||||||||||||
YTD | 2 | ||||||||||||||||||||||||
YTG | 3 | ||||||||||||||||||||||||
Dep | CC | Sort of # | Curr Mon | M/YTD/YTG | |||||||||||||||||||||
A | 1000 | Out | 4 | YTG | 3 | ||||||||||||||||||||
Total | 5 914 |
<colgroup><col span="9"><col><col span="16"></colgroup><tbody>
</tbody>