Sumproduct - multiple criteria for sum

QuestionEdit

QUESTION: I have a spreadsheet with over 3000 lines of data. I have a basic Sumproduct command that I cant get to work. Example: =SUMPRODUCT(--($A$3690:$A$3725=$A3706)*--($B$3690:$B$3725=B$3706),--($G$3690:$G$3725)) Always answers "0" and I know the answer should be 17. I question the data in the cells like in cell A3707 has Ball Valve, V600, 02" Is it having troubles with the commas and quotation mark in the cells? Thank you for any guidance you can provide. Jim McLaughlin

ANSWER: I don't think the text strings are the issue. But they could be. The formula you show is not checking 3000 rows if data, it is only looking at 35 rows of data. That is good, because troubleshooting these kinds of formulas usually works better with smaller data ranges. You should be able to highlight (in the formula bar) the various components of your formula (one at a time) then click F9 to calculate. Each section will turn into a sequence of Trues/Falses. If you include the double minus signs, it will be 1's and zeroes. That will show you which rows of your data pass each of your conditions. If you had 3000 rows you would not be able to do that because the evaluated sequence would be too long.

It does seem peculiar that you are checking both column A and column B to see if they match the same cell (A3706). Also, the third term does not have any kind of test; so that may be the problem. I suspect that you should be testing something or you should not have the -- in front of it. Also, I just noticed that it doesn't have the same rows as the first two terms, so that is definitely a problem. They don't have to be the exact same rows, but, if each term does not have the same number of rows the Sumproduct formula will fail. I thought you would get an error, and not a zero though.

---------- FOLLOW-UP ----------

QUESTION: Ken... thank you for the response. I apologize... the sample line I sent you does only search 35 lines... I was trying to experiment with a much smaller area. Overall there are 3900 lines... unfortunately. In column "A", all entries are descriptions separated by commas like: Ball Valve, V636A, 02" Column B has a 5 letter code like C12FC. The formula needs to match a 2 criteria situation and then, in column G are the quantities with just numbers in that column. After matchin the criteria, Im wanting it to add the quantities from column G. I may be on the wrong track with the formula Im using... any help would be highly appreciated... been fighting this one for a couple of day! ( had to go to the Pros now!)

ANSWER: I think you are using the best formula for the job. I did some testing to confirm that the quotes and commas in the data do not cause any problems.

If you have the same number of rows in each of the arguments in the sumproduct formula you should be able to make it work.

I have been away for a while, so you probably already have this working, but let me know if you are still having trouble.

---------- FOLLOW-UP ----------

QUESTION: Thank you Ken... definitely a big help! Can you give me any ideas what formula to use to be able to do this? Currently I'm using a formula like this: =SUMPRODUCT(--($B$3:$B$3000=B4272),--($E$3:$E$3000=E4272),--($G$3:$G$3000=F4272),--($C$3:$C$3000=C4272),$I$3:$I$3000) If it matches the 4 criterias, it will take the number from that row in column "I" and sum them up... at least thats what I'm attempting to do. Im definitely open to any suggestions

It seems to be working most of the time.... that¡¯s what bothers me.

Thank you for time and help!!!

AnswerEdit

It seems like the formula should to exactly what you want. I suspect that the problem is that some of the data that you expect to qualify for the sum because it looks like it matches, does not really match; maybe some leading or trailing spaces or other characters that you don't see.

You can test on a subset of your data by using F9 to calculate term by term in the formula bar; or you could use conditional formatting to highlight the cells you believe should be summarized then page through you 3000 rows and look for thing that are not highlighted.

Also, you could sort your data by the 4 criteria, delete all the rows that should not be included in the sum, and see if you can track down the reason for some of the ones that should be included not being included.

One peculiar thing in your formula is that you are checking the data in column B against a value in column B, the data in column C against a value in column C, the data in column E against a value in column E BUT the data in column G is checked against a value in column F. Of course that could be correct but it is a little suspicious to me.

Good luck