# sumproduct not calculating correctly

1. ## sumproduct not calculating correctly

Hello, I am working on a project where I need to count how many times a certain phrase appears, while also fulfilling another criteria. Here is the formula I have been using that doesn't seem to be working.

=SUMPRODUCT('05 11 09'!D2:D444="Cannot convert*")*('05 11 09'!G2:G444=1)

It pulls the information off the sheet "05 11 09" and must match the phrase "Cannot convert*" in the D column where it begins with cannot convert and can have any ending. Then it also has to have the value of 1 in the G collumn. When I use this formula, it gives me 0, when it is more than 0. I have tried using the =sum and creating an array, but that hasn't worked either. I can use the countif function for just the first part (counting the cannot converts), but I need it to also match the second criteria.

Any help on this is much appreciated.  Register To Reply

2. ## Re: sumproduct not calculating correctly

try in this format
=SUMPRODUCT(--(\$G\$2:\$G\$444=1),--ISNUMBER(SEARCH("cannot convert*",D2:D444)))  Register To Reply

3. ## Re: sumproduct not calculating correctly

It works out great! I didn't realize the format was the problem

I have one other question if you could help me. I now need to do a similar function where I have to count how many of the "Cannot convert*" appear, but do not count duplicates in relation to another column. What I mean is in column E (E2:E444) there are a list of invoices. I need to create a formula that calculates the amount of times "Cannot convert*" appears with a unique invoice. If "Cannot convert*" appears with an invoice more than once, then the duplicates shouldn't be counted. I am stumped on this and how to tackle it. Any help on this is again very much appreciated.
Thanks!  Register To Reply

4. ## Re: sumproduct not calculating correctly

not sure if you can do that!  Register To Reply

5. ## Re: sumproduct not calculating correctly Originally Posted by martindwilson try in this format
=SUMPRODUCT(--(\$G\$2:\$G\$444=1),--ISNUMBER(SEARCH("cannot convert*",D2:D444)))
You don't really need the wildcard here, the formula works without it. If "cannot convert" is always at the start you could also use LEFT function like this:

=SUMPRODUCT(--(\$G\$2:\$G\$444=1),--(LEFT(\$D\$2:\$D\$444,14)="cannot convert"))

To count different invoice numbers in E2:E444 when D2:D44 is "cannot convert...... try this array formula

=SUM(IF(FREQUENCY(IF(LEFT(D2:D444,14)="Cannot convert",IF(E2:E444<>"",MATCH(E2:E444,E2:E444,0))),ROW(E2:E444)-ROW(E2)+1),1))

formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar  Register To Reply

6. ## Re: sumproduct not calculating correctly

i was about to suggest a helper column say F
with in F2 dragged down
=IF(COUNTIF(\$E\$2:E2,E2)=1,1,0)
then
=SUMPRODUCT(--(\$F\$2:\$F\$300=1),--(\$G\$2:\$G\$300=1),--ISNUMBER(SEARCH("cannot convert*",D2:D300)))
ps i agree on the wild card tho! it was a hangover from some other wild card search and i just put it in!  Register To Reply

7. ## Re: sumproduct not calculating correctly

I used the formula and it seems to be working quite well! Although I understand the basics of what it does, I do not quite understand the workings of the formula. Is the LEFT function to create a substring to allow the extra characters after the "Cannot convert?" Could you explain how the formula works so I can use it as future reference?

Thanks once again for the formula however!

martindwilson,

I originally thought of creating a helper column, but since this will be added to daily, I was trying to find a way to have a main workpage calculate the values without the use of adding columns. I believe your formula would work though.

Also this may seem like a silly question but in the original sumproduct function that I used, do the "--" make it a true/false into a numeric value or something?

Thanks again!  Register To Reply

8. ## Re: sumproduct not calculating correctly

if you put
=ISNUMBER(SEARCH("cannot convert*",D2)) it will return true/false
=--ISNUMBER(SEARCH("cannot convert*",D2)) it will return 1/0
its the 1/0 that the sumproduct uses to calculate  Register To Reply

9. ## Re: sumproduct not calculating correctly

oops wrong post  Register To Reply