Possible to use SUMIF and COUNTIF together? Or other solution?

Been banging my head against Google trying to figure this out.

I've made a Workbook that has a data set that it pulls, let's say, Store Name and Item Inventory. I have checkboxes that when checked will tally up the sum of what is in inventory in one column and what the sum of what was sold in another.

Then I have a column with actual inventory item names - like Apples and Oranges. What I'm trying to do is write a formula so that if the checkbox is "TRUE" then it will sum up what is in inventory/sold according to the item. So if "Store1" and "Store3" are checked it pulls how much total inventory and total sales into two columns, then in a third column will break it down by item.

So the formula is a SUMIF, but I also want it to look at the Data sheet to cross reference what's in B2 (Apples). So kind of like a COUNTIF Data!$C:$C,$B$2 and if that matches then provide the sum from the checkboxed stores.

Attached is a sample sheet I am trying to get it to work on.

Sorry if my explanation is confusing but any advice is greatly appreciated!

Figured it out, so nevermind! :D

So I thought that I had this figured out, but it turns out that I don't. I made a little progress but now it is tallying everything in the column without using the criteria. Attached is an example of a couple different ways I tried to solve this, but for some reason it's ignoring the reference criteria.

I've been spending hours trying to figure this out so was actually going to pay a "Guru" here to help but it seems their PayPal is not active anymore. Any help would be greatly appreciated.

On the Spreadsheet I just attached, I am trying to get it so I can see which "Source" is selling the most of a product (Apples, Oranges). I usually have many "Sources" but sometimes just want to compare a few sources, which is why I made the "TRUE"/"FALSE" checkboxes. So, for example, I could just see how "Source1" and "Source2" are doing as a whole together by checking their checkboxes.

So in the attached data sheet you can see that there are actually only 11 "Apples" and none have "Sold", but the formula is showing 28 "Apples" and 7 "Sold" because it is tallying all the Sources that are checked in the checkbox but not actually looking for "Apples".

I hope that makes sense!

Actually, this Spreadsheet may make more sense because I changed some labels.

You can ignore F5 and F6 - those are just an overall total of what is checked in the checkbox. I'm trying to get C4 and C5 to just show how many "Apples" are coming from each checked source. Instead C4/C5 is basically just tallying everything instead of filtering out just "Apples". In fact,my formulas are not even trying to tally the other items such as "Oranges", "Pickles", "Melons", and "Grapes".

One solution I thought would work is =IF(COUNTIF( Data!$C:$C,$B$3), SUMIFS($J2:$J4,$I2:$I4,"TRUE"), "")

The other I thought would work is =SUMIFS($J:$J,Data!$C:$C,$B3)

But neither of those formulas seem to be taking B3 into account.

Sorry about the profile, I hadn't looked at it since 2011 so forgot it wasn't filled out. I just updated it.

It seems like your Array formula worked but I ran into one problem with it - I usually deal with about 100+ "Sources" (like Source1 and so on). Is there anyway to have the formula look down the entirety of G:G for "TRUE" and do the same calculations as your formula did but not having to have "Source1" and so on in the actual formula?

Thanks again for your help.

Ah-ha! In your post #11 would it be possible to change ($G$2:$G$4=TRUE,$F$2:$F$4) to ($G:$G=TRUE,$F:$F) so it goes down all of both columns, or will I just have to specify how far down it checks?

Yours seems like a great solution though!

Ok, I think with all your help I have almost everything sussed out. One more thing that is probably pretty easy but my brain is fried after working on this all night.

So in C4 (Bought) technically that should tally what was Sold as well, since the Bought number would be the total amount started out with. That would give a true conversion factor of I had a total of X amount, sold Y amount so the conversion of sales is Z amount. Would that be easy to add to the formula?

Sorry if that doesn't make sense...like I said, brain=fried right now.

I guess probably the easiest was would be to just add +C5 to the end of your formula for C4!