Solved

Sumif() function if the criteria is a text formula.

Posted on 2014-03-31
2
333 Views
Last Modified: 2014-04-01
I have a table as below and want to use the sumif function to sum the percentages if the category in the left column starts with "LOCAL".

I have tried among others
=SUMIF($A$8:$A$15,"""LEFT(A8,5)="&"""LOCAL"""&"""",$B$8:$B$15)
but I can't make it work.

Anyone can?

Table for sumif()
0
Comment
Question by:Fritz Paul
2 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39968464
Hi

Enter this Array formula with Ctrl-Shift Enter

=SUM((B8:B15)*(LEFT(A8:A15,5)="LOCAL"))

Open in new window


Regards
0
 

Author Closing Comment

by:Fritz Paul
ID: 39968629
Thanks, I have clean forgotten about array formulas. I had to go read up on them.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now