LinkBack URL
About LinkBacks
I need to show number of items in each price range by category and by catalog appearance. I've tried a number of SUMIFs with no success. I've attached a sample. Thanks in advance!
I need to show number of items in each price range by category and by catalog appearance. I've tried a number of SUMIFs with no success. I've attached a sample. Thanks in advance!
Something like this?
Formula:
Please Login or Register to view this content.
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
When I use that formula in my actual sheet, I get the "Excel ran out of resources while calculating one or more formulas..." message.
When applied to your sheet, what (exactly) was the formula used?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I can imagine Excel would say that if you would put in entire columns instead of limited ranges. If that is the case, there are two options.
One is to convert your datatable to an Excel Table. It expands the range automatically so the formulas can stay the same as you add data.
Two is to create dynamic named ranges. They also expand the range automatically.
Which way to go depends on your setup. Do you expect the number of books to increase?
Last edited by Jacc; 02-25-2017 at 05:16 AM.
I see no place in the 'Summary' sheet to account for this and am unclear what you mean.I need to show number of items in each price range by category and by catalog appearance.
Without that to consider I tried this SUMIFS formula. It returns what I believe to be correct results.
=SUMIFS(INDEX(Data!$D:$O,,MATCH($A$3,Data!$D$1:$O$1,0)),INDEX(Data!$D:$O,,MATCH($A$3,Data!$D$1:$O$1,0)),">="&B$5,INDEX(Data!$D:$O,,MATCH($A$3,Data!$D$1:$O$1,0)),"<"&C$5,Data!$C:$C,$A8)
Like Jacc I put a large value of 1E+306 in cell J5, but I get different results.
Last edited by FlameRetired; 02-25-2017 at 09:18 PM.
Dave
Interpreted another way.
=SUMPRODUCT(($A8=Data!$C$3:$C$6)*(Data!$P$3:$P$6 > = Summary!B$5)*(Data!$P$3:$P$6 < Summary!C$5)*Data!$P$3:$P$6*INDEX(Data!$D$3:$O$6,,MATCH(Summary!$A$3,Data!$D$1:$O$1,0)))
Thank you all for your help. I found a way to make it work.
Spamanda,
So would you post the solution you used?
It might be helpful to other forum members seeking solution to a similar problem.
can I ask here for help like cataloging all files from folders into excel using VBA/macro buttons?
https://www.excelforum.com/excel-programming-vba-macros/1210652-how-to-catalog-pdf-png-files-into-excel-with-filers-using-vba-buttons.html#post4794372
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks