Reputation: 4737
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
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
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