Last Edited By **Krjb Donovan** **Last Updated:** Mar 05, 2014 09:23 PM GMT

QUESTION: I want to sum up what falls in the cells that are to the right of a particular(i.e. Black) and below another(i.e. Size Small). This would allow me to summate the total quantity where a matrix hit Black/Size Small for multiple locations.

Please let me know if you can help! Feel free to ask questions if I am not clear. Thank you!

ANSWER: Can you please clarify if you want to count the number of instances of these, or if you want to literally sum the numbers that are in the cells.

---------- FOLLOW-UP ----------

QUESTION: I literally want to sum the numbers that are in the cells so that I would have a total count. For example if someone ordered 10 black shirts that were size small at one location and a completely different location (same spreadsheet) they ordered 5 black shirts that were size small. I would want to know that there were a total of 15 black/small shirts.

Although this sounds simple, since I have over 10 colors and 5 sizes and many stores, I need to revert to a more complex formula.

one more question, sorry.

Do you really mean it's directly to the right of the Black and directly under theSmall?? I'm trying to picture a spreadsheet laid out like this.

or do you just mean, Black is way over to the left, and Small is way up at the top, and this number intersects it somewhere - maybe 40 rows down and 10 rows over.

It sounds very much to me like a pivot chart or a pivot table would give you this graphical and literal representation of what you are looking for.

If you are pretty sure that's not the case, and if you're sure you're looking to sum number-cells where the cell directly left is Black and the cell directly above is Small ... and these number cells can be anywhere in the sheet ... then I think I will go to a VBA solution where it loops through all cells in the sheet and builds up a sum of cell values in instances where cell to left is black, and cell to right is small.

but please clarify the precise layout of the sheet, if you want, send me one at ipisors at yahoo dot com

Advertisement

©2021 eLuminary LLC. All rights reserved.