Summing data based on several cell contents

**JEB** · 06-04-2014, 12:26 PM

Wonder if someone can help with this please. I need a formula to give a combination of IF and I think SUMMIFS to calculate totals based on certain cell criteria. Result to be:

Cell C21 = Total of C2 to C16 where value in column A = UK and value in Coumn B = Orange
Cell C22 = Total of C2 to C16 where value in column A = UK and value in Coumn B = Apple
Cell C23 = Total of C2 to C16 where value in column A = UK and value in Coumn B = Pear

Cell C25 = Total of C2 to C16 where value in column A = USA and value in Coumn B = Orange
Cell C26 = Total of C2 to C16 where value in column A = USA and value in Coumn B = Apple
Cell C27 = Total of C2 to C16 where value in column A = USA and value in Coumn B = Pear

Any assistance would be greatly appreciated.

**Sam Capricci** · 06-04-2014, 12:31 PM

because you have the word total in it i would use this in c21-c24 =SUMIFS($C$2:$C$16,$A$2:$A$16,"UK",$B$2:$B$16,B21) and this in c25-c27 =SUMIFS($C$2:$C$16,$A$2:$A$16,"Usa",$B$2:$B$16,B25).

**Glenn Kennedy** · 06-04-2014, 12:32 PM

Hi. I think that this is what you want.

**AlKey** · 06-04-2014, 12:37 PM

For UK

=SUMPRODUCT(($A$2:$A$16=LEFT($A$21,FIND(" ",$A$21)-1))*($B$2:$B$16=$B21)*($C$2:$C$16))

For USA

=SUMPRODUCT(($A$2:$A$16=LEFT($A$25,FIND(" ",$A$25)-1))*($B$2:$B$16=$B25)*($C$2:$C$16))

**Sam Capricci** · 06-04-2014, 12:49 PM

cheating off Alkey's formula, you could adjust mine to be... =SUMIFS($C$2:$C$16,$A$2:$A$16,LEFT($A$21,FIND(" ",$A$21)-1),$B$2:$B$16,B21)

**Glenn Kennedy** · 06-04-2014, 01:01 PM

...and it would have helped if Id attached the file! Oh well, you now have several other answers...

**JEB** · 06-04-2014, 01:07 PM

Sorry folks I've tried all of the formulas above in cell C21 and it comes back with #NAME?. I can't see what I am missing here.

**Sam Capricci** · 06-04-2014, 01:09 PM

both of mine worked in your attachment.

EDIT: I also tested Alkey's in your attachment and it worked.

**JEB** · 06-04-2014, 01:09 PM

Originally Posted by Glenn Kennedy

Hi. I think that this is what you want.

Glen if you could copy up your file that'd be really appreciated thanks.

**JEB** · 06-04-2014, 01:10 PM

Originally Posted by Glenn Kennedy

Hi. I think that this is what you want.

Glen if you could copy up your file that'd be really appreciated thanks.

**AlKey** · 06-04-2014, 01:10 PM

Please see attached file. Hopefully it will help.

**Glenn Kennedy** · 06-04-2014, 01:22 PM

OK. Here you are (after a slight delay...)

**JEB** · 06-04-2014, 01:23 PM

Thanks for all the input folks - sorted - many thanks.

**Glenn Kennedy** · 06-04-2014, 01:45 PM

If that's it can you mark the thread as solved and consider saying thanks to those who helped by clicking the "Add reputation" button at the bottom of the post(s)?

**AlKey** · 06-04-2014, 07:00 PM

Thank you for the feedback!

**JEB** · 06-05-2014, 07:14 AM

I thought I had this cracked, but still having issues. In the attached file I've used the forumla in column B of the "Summary by Country" tab to add up data based upon country and esource type in the "Test data tab". It works fine for resource type = Eng 1, but is not counting up the date for any other resource type.

I've been looking at this all morning and can't figure out what may be wrong. Would apprecite any thoughts on what I am doing wrong here please - thanks again.

**Sam Capricci** · 06-05-2014, 07:22 AM

your issue is in your merged reference cell in the test data sheet where you merged UK down rows 3-15. the formula is seeing it only once in your summary sheet, if you have UK in each row it will work (same as US). there may be another way but that is just my quick assessment.

EDIT: UK exists only in cell A3 and US only in A16 so the formula isn't seeing it again in A4, A5 etc.

**JEB** · 06-05-2014, 07:32 AM

Originally Posted by Sambo kid

your issue is in your merged reference cell in the test data sheet where you merged UK down rows 3-15. the formula is seeing it only once in your summary sheet, if you have UK in each row it will work (same as US). there may be another way but that is just my quick assessment.

EDIT: UK exists only in cell A3 and US only in A16 so the formula isn't seeing it again in A4, A5 etc.

Great spot, that's exactly what it is, many thanks again.

**Sam Capricci** · 06-05-2014, 07:33 AM

you're welcome, and thanks for the feedback.

Summing data based on several cell contents

LinkBack

Thread Tools

Rate This Thread

Display

Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Re: Summing data based on several cell contents

Thread Information

Users Browsing this Thread

Similar Threads

Tagging rows of data based on cell contents

[SOLVED] Summing multiple values based on cell contents

Rows of data based on cell contents

Need to combine 2 rows of data into one line while summing their contents

summing up data in an adjacent cell based on date into a calendar.

Bookmarks

Bookmarks

Posting Permissions