Complicated Nested Excel Function

Question

I'm working on a function in Excel that will allow me to sum columns if the column before it contains the word "pre"

here's a mock spreadsheet:

                   Grade          Points Possible

C++ Pre Test         20                   20
Homework Week 1      30                   30
C++ Test             90                  100

Now I want to use the SUMIF() function with a SEARCH() function nested in it that will examine cells A2 through A4 and if the word 'pre' is found it will sum the appropriate number in column B.

Here's what I have so far that doesn't seem to be working:

=SUMIF(A2:A4,SEARCH("pre",A2:A4),B2:B4)

What I intend for this to say is "If the word 'pre' is found in any cell in the range a2:a4, pull the value from its corresponding grade in column b". The output I am expecting with the mock spreadsheet is '20' since pre only appears once.

Perhaps a VB macro would be in order?

EDIT: Upon further investigation maybe the LOOKUP() function may be best suited since SEARCH() is used to return a value from the column it's searching.

Riley Lark · Accepted Answer · 2010-11-24 20:06:35Z

3

=SUMIF(A2:A4, "=*pre*",B2:B4)

answered Nov 24, 2010 at 20:06

Riley Lark

20.9k15 gold badges86 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Caley Woods Over a year ago

Thank you, this solved what I was going for and in my more complex actual spreadsheet it ended up in an average function like so: =AVERAGE(SUMIF(A4:A100,"=*pre*",B4:B100),COUNTIF(A4:A100,"=*pre*"))

Patrick Honorez Over a year ago

Seen in the introduction of an MS Excel macro training book in 1991: "The best macro is no macro". So true...

Thea · Accepted Answer · 2012-02-20 11:35:07Z

1

The above will not work. If the array more than 2 (I know it is) you can extend this on up to 30 iterations for the sumproduct function

=SUMPRODUCT(B2*IF(ISERROR(SEARCH(" Pre ",A2)),0,1),B3*IF(ISERROR(SEARCH(" Pre ",A3)),0,1))

I do not really see any difference if you use sum or sumproduct and while 30 is the max, you just do multiples like =SUM() + SUM() would make 60.

Another option is to use column C and inert this into C1 and copy it on down

=IF(ISERROR(SEARCH(" Pre ",A1)),"",B1)

and sum at the end.

Next option is as you mention - a macro with a rolling sum using for i, next i

edited Feb 20, 2012 at 11:35

Thea

8,0656 gold badges30 silver badges41 bronze badges

answered Nov 25, 2010 at 8:07

Dave

112 bronze badges

Comments

Thea · Accepted Answer · 2012-02-20 12:47:59Z

0

Take apart your formula.

=Search("Pre",A2) returns 5 - I don't think you want that in your sumif.

Try this instead:

Create a new column D that looks for the word "pre" in column A. Maybe something like =iferror(Search("Pre",A2),1,0)) [If Pre exists in that cell, return 1, else return 0]

Now sumproduct(B:B,D:D) should give you points earned on pretests, and sumproduct(C:C,D:D) should give you points possible.

edited Feb 20, 2012 at 12:47

Thea

8,0656 gold badges30 silver badges41 bronze badges

answered Nov 25, 2010 at 17:25

MattCope

1

Collectives™ on Stack Overflow

Complicated Nested Excel Function

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related