Hi - Dave here.
One of the most commonly used functions in Excel is the SUMIFS function. You'll see SUMIFS in all kinds of formulas that do a
conditional sum.
The basics are easy. If you want to sum cells in B1:B10 when cells in A1:A10 contain the color "Red", you can use SUMIFS like this:
=SUMIFS(B1:B10,A1:A10,"red")
Simple. But what if you need to do a SUMIFS based on a
substring in another cell? For example, sum by state, where state appears
embedded in other text?
This is one of those Excel problems that is not exactly difficult, but is not easy either. The trick is to use wildcards, but the wildcards need to be
concatenated to a cell reference
inside the criteria:
[
download the workbook and read the full explanation]
One limitation of the SUMIFS function is that it's not case-sensitive. If you need a case-sensitive formula, you can roll your own with the SUMPRODUCT and FIND functions. The article above explains both approaches. Click the link above to read the explanation and download the workbook to see how it works.
Holiday sale continues!
The key to mastering Excel is
practice.
Right now, you can save 25% on all of our training. Here's what you get:
- Bite-sized video training - one topic in 2-3 minutes.
- Practice worksheets - learn by doing.
- Save videos for future reference in your own list.
- Focused - no extra clutter or complexity.
- Core skills you can use again and again.
- Lifetime access - work at your own pace.
- No ads when you are logged into Exceljet.
- 100% satisfaction guarantee - we want you to be happy :)
Use this special link to save 25% until December 23:
https://exceljet.net/training?code=HOLIDAY22
Free Excel resources
Although we offer paid training, we also have many free resources:
https://exceljet.net/excel-topics
Have a great weekend!
Dave
