• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Horizontal-Vertical SUMIFS not working properly, used SUMPRODUCT

A

Amit Punia

Member
I needed a formula which can take 2-3 criteria and based on those criteria it updates the value in the cell. SUMIFS function works perfectly in such cases, however it's of no use when criteria are in horizontal rows and vertical columns. So I used SUMPRODUCT formula to overcome this problem. Now the formula is working absolutely fine, but there is also a slicer in the file next to the table. If I select some of the companies from the slicer then the formula doesn't work properly.
The file is attached for reference. The formula is in range("AY4:BE9"). When all the items are selected in the slicer then the formula works perfectly. The moment I select a single item from the slicer, it shows #value! error instead of the correct value from the table.

Kindly guide.
 

Attachments

  • Automobile Companies Slicer V8.0 - VBA.xlsm
    208.1 KB · Views: 6
What exactly is the purpose of this? As far as I can see, the values produced won't change when you filter, so why bother filtering at all. What are you trying to achieve?
 
The values won't change, that's true. But I want these numbers to go in the chart which is below the table. The chart should be dynamic and should only show companies which are visible in the table. Using the filter, if I want to see the data in the chart only for one company then this formula will help. Let me know if there is any other alternative to do so.
 
OK, so tell me how you've set up the slicer. The problem with a chart is it will only work using data from visible cells. Do you want to be able to show more than one company at once when you filter, or just one? If the latter, then a drop-down selection might work better.
 
Actually the slicer is not just for table and chart. This slicer is coming from another sheet where all the pivot tables are. This slicer is controlling other things too. That's why I need to use this slicer only. I need to select more than one company at a time for comparison, so can't use drop down selection.

Just wanted to know why the formula is working perfectly when all the items are selected in the slicer and not when one or few of them are selected. I even used "Evaluate Formula" to understand the same. But it's Boolean function, not able to understand what is multiplying with what to form #value error in the output.
 
@GraH - Guido, You're absolutely right. Actually sometimes we see a problem and start attacking it with everything we know. We start solving problems with complicated functions. And in excel we can see the same problem from 100 different angles. Thank you so much for a simplified solution.
And thanks for sharing those links with me for company comparison. I started working on that file initially(didn't have the link - how to make something like that). Prepared it without the steps. Did reverse engineering to prepare the same. Now when you shared the link, I came to know that there was something like that too to make my life easier. If you check the file which you shared with me, there is a sheet which is "xlSheetVeryHidden"(I did the same in that sheet). Thanks again.

@AliGW - Thanks you very much for reminding me that there is no need to do all this. There are easier solutions to it. Thank you very much :)
 
You must log in or register to reply here.
Back
Top