Aug 23 2019 07:01 AM - edited Aug 26 2019 06:01 AM
I am getting errors using the sumifs function.
See attached file.
Appreciate any help.
Thank you.
Aug 23 2019 12:31 PM
@bb94twins Can you upload the sample file? It would be helpful to see the tabs and arrays in your spreadsheet. Happy to take a look.
Aug 23 2019 01:22 PM
Sum range in SUMIFS shall be 1D range, not 2D. Try to use SUMPRODUCT instead, like
=SUMPRODUCT('IS Detail By Company Base Accou'!$E$10:$AK950*('IS Detail By Company Base Accou'!$A$10:$A$950=$C8)*('IS Detail By Company Base Accou'!$E$3:$AK$3=E$6))
Aug 26 2019 06:02 AM
@excelgeek I have attached a file with some example data.
Aug 26 2019 06:03 AM
@Sergei Baklan I had tried your suggestion, but wasn't able to get it to work either. are you able to make adjustments to the file and repost? I just need to sum by country listed.
Thanks.
Aug 26 2019 06:30 AM
SolutionIf 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
Aug 26 2019 09:34 AM - edited Aug 26 2019 09:36 AM
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.
Aug 26 2019 10:14 AM
Thanks @Peter Bartholomew . Will need to play around with the Choose and index functions as I am not too familiar with them.
Appreciate the feedback and insight
Oct 18 2019 08:53 AM - edited Oct 21 2019 09:18 AM
Hi @Sergei Baklan ,
I attempted to replicate the formula you had created for me into another file, but apparently I am doing something wrong. Would appreciate your help again if you could please review the attached and advise on what I need to adjust. The cells are on the first tab highlighted in green.
Thank you.
Aug 26 2019 06:30 AM
SolutionIf 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