Sum of Table given two criterias

[RazedC]

Hi all,

So lets say I have this table (generated from Software, can't change format) - attached.

If I wanted to get the total sum of "Oranges" from "Basket 4", what would be the formula to do so? I'm trying to design a Excel Template atm (for future use), so in the future there might be more "Baskets" added for each, such as Basket 2 and 3 for "Oranges"

Also, is it possible to sum up to a certain date as well? Like Sum of Pineapples from Basket 3, from Jan - Sep and then next month it would be Jan - Oct?

Thanks for all the help provided,

[oeldere]

Yes, those things can be done.

1) You are familiar with pivot table?

2) You are alowed to work with macro's?

[RazedC]

Hi oeldere,

Im still a beginner when it comes to some of the more advanced Excel stuff, and haven't used Pivot Tables that much. I think its okay to use macros, but I've never used them before.

Thanks,

[JBeaucaire]

1) Highlight A2:A11 (or however far down the data goes.)
2) Press CTRL-G > Special > Blanks
3) Carefully press = then the UP arrow
4) Now press CTRL-ENTER to enter that formula into all the blank cells at once. This fills in the missing category cells.

Now the formulas demonstrated here will work.

[oeldere]

We gonna do this step by step.

It will be maybe to quick for you, but then you have to ask for an time-out so I could explain it to you.

First of all, we need to fill the data in column A, with the names of the fruits.

We need this data for the pivot table we gonna use.

Push on the button and see what happens.

Behind the button is a code (VBA) which will fill the data in column A.

You have to allow Excel to use macro's (otherwise the code will not run).

Try this and reply back.

See the attached file.

[RazedC]

Alright, got the data in column A filled out by using the macro.

[newdoverman]

This uses a Pivot table to summarize your data.

[oeldere]

Now we gonna rearange the data so it can be easy used for an pivot table.

I used a code to do that.

I put it behind button 2.

Notice I filled cell a1 and cel b1 with text.

Notice there is no sheet Output => it will make itself.

Sheet Output will be filled with all the data in your sheet.

After this we gonna make the pivot table.

Try step 2 and please respond back.

[RazedC]

Alright got the Output sheet avaliable.

[oeldere]

Now we gonna make the pivot table.

Since you use Excel 2010 it's maybe a little diffirant, but let's take a try.

See the attached file for the (first) result.

We gonna expand this table after this.

I posted also an word doc with the text in it.

Do you have questions or comments, just ask or comment.

text in the word file.
Click in a cell of the table (e.g. b3)
Excel 2007 => Insert => pivot table
You find these text in the table Output!$A$1:$D$109
Select your location for the pivot table new worksheet
You will see the lay out of the pivot table on the left side of your sheet.
On the right side you see some Boxes with the text:

Fruit drag this one in the box row(value)
Basket
Month drag this one in the box row(value)
Value drag this one in the box values (and choose sum)

Format the value (after it’s dragged in the box values (and been summed) to value with 2 decimals.

The result should be, like in my file.

After that you can e.g. drag the Baskets in the column box
You get an new pivottable, with the new values in it.

If you don’t want to see the Baskets in the column, just drag them back from the column Box to the list.
Now you need playing (drag and drop) to see what kind of information you need.
If you need help by that, just ask.

[JBeaucaire]

Post #4 has an already working solution using basic formulas, too. Be sure to check it out, too.