Hello, I am hoping you can help.
Im trying to convert a SUMIF formula in excel 2010 to a SUM(IF.
The original formula is
=SUMIF('F:\[test1.xlsx]Sheet1'!$A$2:$A$6,"*"&B1&"*",'F:\[test1.xlsx]Sheet1'!$B$2:$B$6)
Evaluate the formula results in
B1=Bob
It then checks for the word appearing in the range specified
for clarification
this results in the correct answer of 9
however if test1.XLSX isn't open I get a #value error
So I searched and found that "Sum(IF" is a good workaround so the test1.XLSX could remain closed
here is my workaround
=SUM(IF('F:\[test1.xlsx]Sheet1'!$A$2:$A$6="*"&B1&"*",'F:\[test1.xlsx]Sheet1'!$B$2:$B$6,0))
But it doesn't work, and I don't understand why.
it returns the result 0 (no matter if I use enter or CRTL, Shift and enter)
Evaluation shows
SUM(IF{"bob is great";"Bob is pants";"BoB is the best";"cats cool";0}="*Bob*",'F:\[test1.xlsx]Sheet1'!$B$2:$B$6,0))
all seems okay
but next step reads
SUM(IF{FALSE;FALSE;FALSE;FALSE;FALSE}",'F:\[test1.xlsx]Sheet1'!$B$2:$B$6,0))
then as it cant seem to see the words "Bob" comes back with 0
I could really do with some help on this. Two days is too long to be working on this.
Thanks for any assistance you can provide
Im trying to convert a SUMIF formula in excel 2010 to a SUM(IF.
The original formula is
=SUMIF('F:\[test1.xlsx]Sheet1'!$A$2:$A$6,"*"&B1&"*",'F:\[test1.xlsx]Sheet1'!$B$2:$B$6)
Evaluate the formula results in
B1=Bob
It then checks for the word appearing in the range specified
for clarification
bob is great | 1 |
BOb is pants | 2 |
boB is the best | 6 |
cats cool | 7 |
this results in the correct answer of 9
however if test1.XLSX isn't open I get a #value error
So I searched and found that "Sum(IF" is a good workaround so the test1.XLSX could remain closed
here is my workaround
=SUM(IF('F:\[test1.xlsx]Sheet1'!$A$2:$A$6="*"&B1&"*",'F:\[test1.xlsx]Sheet1'!$B$2:$B$6,0))
But it doesn't work, and I don't understand why.
it returns the result 0 (no matter if I use enter or CRTL, Shift and enter)
Evaluation shows
SUM(IF{"bob is great";"Bob is pants";"BoB is the best";"cats cool";0}="*Bob*",'F:\[test1.xlsx]Sheet1'!$B$2:$B$6,0))
all seems okay
but next step reads
SUM(IF{FALSE;FALSE;FALSE;FALSE;FALSE}",'F:\[test1.xlsx]Sheet1'!$B$2:$B$6,0))
then as it cant seem to see the words "Bob" comes back with 0
I could really do with some help on this. Two days is too long to be working on this.
Thanks for any assistance you can provide