Hi.
How can i convert this sumifs formula to Sumproduct?
Im facing value errors and I need to update the links every time.
I get a div error as well.
I have slightly edited the path here for confidentiality
Hi.
How can i convert this sumifs formula to Sumproduct?
Im facing value errors and I need to update the links every time.
I get a div error as well.
I have slightly edited the path here for confidentiality
Last edited by jay.tee; 10-18-2021 at 01:09 PM.
i get the value
0 instead of $9000000
Last edited by jay.tee; 10-18-2021 at 01:10 PM.
original formula : =SUMIFS([2021.xlsx]!O$169:O$278,[2021.xlsx]!$B$169:$B$278,$B4,[2021.xlsx]!$C$169:$C$278,"Yes")
Last edited by jay.tee; 10-18-2021 at 01:10 PM.
Try this:
=SUMPRODUCT([2021.xlsx]Construction!$O$169:O$278
*([2021.xlsx]Construction!$B$169:$B$278=$B4)
*([2021.xlsx]Construction!$C$169:$C$278="Yes"))
Last edited by Phuocam; 10-17-2021 at 04:02 AM.
Thanks!
the formula is returning 0. it is not matching the $B$169:$B$278=$B4.
the evaluation steps show that this is all false. however its not right.
Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.
Can you upload 2 files?
onedrive is my main file.
the other one is supporting. i have slightly renamed the files and data in it.
because I run into the value error, I am trying to change the formalua a bit as the sumifs requires to keep other workbooks open if an external source is used.
now as per the google suggestion I'm trying to change one drive workbook cell F4 formula
Last edited by jay.tee; 10-18-2021 at 01:11 PM.
Change to:
=SUMPRODUCT([2021.xlsx]Construction!$O$169:O$278
*([2021.xlsx]Construction!$B$169:$B$278&""=$B4&"")
*([2021.xlsx]Construction!$C$169:$C$278="Yes"))
Thank you so much Phuocam!
but will this file be opened easily in my manager's computer?excel wouldn't magically know the files and the paths...
the sum ifs was returning the value errors. but as google suggested to change it to sum product.
I have list of sheets that are my activities.
How can I make the formula read the sheet name from this cell
so instead of sheet it takes the value in the cell
Last edited by jay.tee; 10-18-2021 at 01:11 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks