Sumproduct conditioning on the first characters

**Orongo** · 06-16-2016, 03:59 AM

Hi, I have here a small sample of my large data set. I want a formula in cell E2 to return the number of pages for the chapter given in cell E1. The problem is that some chapters begins with the same numbering and I only want number of pages for exact given chapter in cell E1. So far I have set up a sumproduct for efficiency, and match because it is a string (though not working). Also I get the results from all chapters beginning with number e.g. seach for 1.1 and I get 1.1, 1.1.0, 1.1.1 and so on. How can I set a formula that only gives me number of pages for chapter 1.1 and no other suffixes?

**shirleyxls** · 06-16-2016, 04:13 AM

Try this:

=SUMIF($A$2:$A$7,$E$1,$B$2:$B$7)

This gave me an answer of 100 when I put 1.1 in E1

**Orongo** · 06-16-2016, 05:15 AM

Thanks that works great. How can the formula be changed if I want pages for all chapter starting with 1.1?

**shirleyxls** · 06-16-2016, 05:46 AM

An array formula would do the trick in E2:

{=SUM(IF(LEFT($A$2:$A$7,3)=$E$1,$B$2:$B$7))}

Don't forget to press Ctrl+Shift+Enter when you put this in to make sure it's an array formula.

By the way, you would need to enter E1 as text for this to work

**Phuocam** · 06-16-2016, 07:14 AM

Maybe like this ...

=SUMIF($A$2:$A$7,$E$1&"*",$B$2:$B$7)

**Orongo** · 06-17-2016, 08:27 AM

Hi the answer I get is not 100 as I want. So only want the number of pages for just chapter 1.1

Sumproduct conditioning on the first characters

LinkBack

Thread Tools

Rate This Thread

Display

Sumproduct conditioning on the first characters

Re: Sumproduct conditioning on the first characters

Re: Sumproduct conditioning on the first characters

Re: Sumproduct conditioning on the first characters

Re: Sumproduct conditioning on the first characters

Re: Sumproduct conditioning on the first characters

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] SUMPRODUCT & wildcard characters

SumProduct, IF, First 3 Characters

Using Wildcard characters in sumproduct

[SOLVED] Using Wildcard characters in sumproduct

[SOLVED] Using Wildcard characters in sumproduct

[SOLVED] Using Wildcard characters in sumproduct

Using Wildcard characters in sumproduct

Using Wildcard characters in sumproduct

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions