CODEWITHSUNDEEP
excel
Caley Woods
Caley Woods

Reputation: 4737

Complicated Nested Excel Function

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.

Upvotes: 0

Views: 681

Answers (3)

MattCope
MattCope

Reputation: 1

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.

Upvotes: 0

Dave
Dave

Reputation: 11

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

Upvotes: 1

Riley Lark
Riley Lark

Reputation: 20890

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

Upvotes: 3

Related Questions