Forum Discussion
SumIfs Help
If adjust formula to your ranges it'll be
=SUMPRODUCT('IS Detail By Company Base A (2)'!$C$10:$AK$950*('IS Detail By Company Base A (2)'!$A$10:$A$950=$C8)*('IS Detail By Company Base A (2)'!$C$3:$AK$3=E$6))Please check attached
Whilst it is possible to use ranges other than a simple column array, it is rare that such a strategy is useful. The problem with the x_IFS() functions are that the criterion parameters must be range references of the same shape as the data you are processing. Thus the country row and the revenue category columns would each need to be broadcast to fill a 2D table.
Something that would be possible in your example would be to down-select the 2D data array to a single row by using
= CHOOSE( index, row1, row2 … )
or INDEX
= INDEX( dataTable, index, 0 )
before using SUMIFS with the country criterion only.
Thanks PeterBartholomew1 . Will need to play around with the Choose and index functions as I am not too familiar with them.
Appreciate the feedback and insight
