Hi,
I have 3 instances where I need the SUMPRODUCT instead of SUMIF and SUMIFS since the results are #VALUE in my SUMMARY file when the DATA workbook is closed. After several unsuccessful tries I've attached a file with the 3 cells highlighted where I would like to use the SUMPRODUCT formula.
Any suggestions would be appreciated.
Thanks,
Phillycheese
Last edited by Phillycheese5; 03-12-2019 at 01:28 PM.
Please try at C20 and Press Ctrl+Shift+Enter
=SUM($G$8:$G$12*($F$8:$F$12=TRANSPOSE($C$8:$C$16)))
Why not use cells D8:D16 to get the individual country values and then let C20 sum the appropriate values from D8:D16. You'll need to list the region name agoanst each of the C8:C16 countries,
Therefore in D8 copied down
Would you also update your location please. It often helps if we need to consider things like regional settings and date formats.
Please Login or Register to view this content.
Last edited by Richard Buttrey; 03-11-2019 at 01:13 PM.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi Bo_Ry, I tried the formula within the file I posted and it works great. But when I tried it in my actual file, which references an external file, it didn't work. Any ideas?
Please Try cut C20 and paste to other files
or this with CSE
=SUM([Formula_Help_SUMPRODUCT.xlsx]Sheet1!$G$8:$G$12*([Formula_Help_SUMPRODUCT.xlsx]Sheet1!$F$8:$F$12=TRANSPOSE([Formula_Help_SUMPRODUCT.xlsx]Sheet1!$C$8:$C$16)))
Hi Richard, yes that wouldn't be so bad if it were only 1 or 2 lists. But in my actual file I have many lists of countries and regions, for many different portfolios. There are also a lot of other formulas so it would be a big re-work to get the data switched around in my actual file. That's why I'm hoping for a formula solution.
Hi Bo_Ry, the formula basically ends up being like this:
=SUM([DATA.xlsx]Sheet1!$G$8:$G$12*([DATA.xlsx]Sheet1!$F$8:$F$12=TRANSPOSE([SUMMARY.xlsx]Sheet1!$C$8:$C$16)))
The list of countries I'm trying to match against is in my SUMMARY file whereas the country names that I'm trying to match, along with corresponding percentages, is in the DATA file. Even with the DATA file open I'm getting a #VALUE result so I'm not sure why that is the case. Yes I am! I just did a test and it seems like the G$8:G$12 needs to be the entire list. In my actual DATA file I could have a list of 50 or 100, with headers and/or blank spaces so I used a range of G$1:G$200 to ensure I would grab all the data. Is there any modification to the formula that would allow for that?
Maybe try this with CSE
=SUM(iferror([DATA.xlsx]Sheet1!$G$8:$G$12*([DATA.xlsx]Sheet1!$F$8:$F$12=TRANSPOSE([SUMMARY.xlsx]Sheet1!$C$8:$C$16)),))
or
=SUM(IFERROR(INDEX([DATA.xlsx]Sheet1!$G$8:$G$12,N(IF(1,MATCH([SUMMARY.xlsx]Sheet1!$C$8:$C$16,[DATA.xlsx]Sheet1!$F$8:$F$12,)))),))
Hi Bo_Ry, I tried the first formula and tested it when the workbook was closed and that works great! I appreciate the help!! I think the other formulas are less complex as one tests for a single criteria and the other tests for two criteria. I will try and tweak what you sent and will see if I can get them to work.
Hello, I tried various combinations using the SUMPRODUCT formula like the following with no luck:
=SUMPRODUCT(--([external.xlsx]DATA!$E$12:$E$2000,">50000"),([external.xlsx]Data!$B$12:$B$2000))
Similarly, I tried iterations of the formula provided by Bo_Ry without success.
If I could ask for some additional help that would be great.
Try
=SUMPRODUCT(--([external.xlsx]DATA!$E$12:$E$2000>50000),([external.xlsx]Data!$B$12:$B$2000))
Amazing what a couple of misplaced quotation marks can do to a formula! Thanks Bo_Ry
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks