Donate SIGN UP

Excel Sumif and Concatenate

Avatar Image
funkylad20 | 11:23 Tue 01st Jun 2010 | Computers
4 Answers
I have a big spreadsheet with a whole host of data.
It essentially lists a figure (grant award) in different counties of England, with one linear entry per award (ewith other data also)

Some awards are labelled as R, some as C, and some as M.

I need to obtain the sum of all with the label 'R' and the individual county (i.e. the sum of all awards for Bedfordshire labelled as M).

I think it has something to do with sumif and concatenate but am really stuck...
Gravatar

Answers

1 to 4 of 4rss feed

Best Answer

No best answer has yet been selected by funkylad20. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
Not sure I 100% get the problem, but a quick look in the Excel help suggests CountIf might be a better solution since 'C', 'M', and 'R', and not numeric values.

COUNTIF(range,criteria)
i.e. =COUNTIF(A1:A756,"C")
The formula you need is SUMPRODUCT()
http://www.google.co....-8&q=Excel+SUMPRODUCT
SUMIF will work even if your criteria are alphabetical.

So if your labels are in column A and the data that need summing are in column B, you'd have:

=SUMIF(A1:A1000,"R",B1:B1000) - make row counter as large as needed, of course
ah, sorry, did not read the question correctly. If you have multiple alphabetical criteria in different columns (A & B) you can create an additional column C where C1 = A1&B1, so C1 would read "MBedforshire" and then you can use column C as your "sum criteria" column.

1 to 4 of 4rss feed

Do you know the answer?

Excel Sumif and Concatenate

Answer Question >>

Related Questions

Sorry, we can't find any related questions. Try using the search bar at the top of the page to search for some keywords, or choose a topic and submit your own question.