Using Sumproduct on blank cells

[mjohnston0209]

I am trying to pull information from one file to another that will be used in a report. In the data source file, there is a cost category (i.e. materials, subcontractor) which is further broken down into a specific vendor and how much each spent on a monthly basis. In the file used for the report, I just need to know how much was spent on materials and subcontractors on a monthly basis. The desired format is in the attached image.

The Sumproduct would normally work, however, for every blank cell in the data source file there is a formula that enters "" to make the cell appear blank. This is creating an error when you reference all the cells. I could reference the specific cells with values, but I am constantly adding vendors, which would require me to modify the formula. Is Sumproduct the correct formula to use here, or should I use Index Match which I have had no luck with as well.

 

[mjohnston0209]

I should also mention that I do not want to use sumif because if the data source file is closed, the sumif formulas return errors.

 

[Fluff]

What is the formula you are using for that example?

 

[mjohnston0209]

What is the formula you are using for that example?

I don't believe my formula will work because I am using both a column and row for the conditions. Based upon past experience, the criteria has to be in the same direction and height (i.e. B10:B20 & C10:C20)

 

[Fluff]

You can use both rows & columns as criteria in sumproduct.

 

[mjohnston0209]

What is the formula you are using for that example?

Would you know of another formula? I originally thought of using index match array to narrow down the rows and specific column, but every example I find uses multiple criteria to narrow down the answer to a specific cell. I can't find an example of summing various rows with index match on just one criteria.

 

[Fluff]

Why not use sumproduct?

 

[mjohnston0209]

Why not use sumproduct?

are we able to attach a file so you can look examine exactly what I have?

 

[Fluff]

Can you just post the formula you said isn't working?

 

[mjohnston0209]

Can you just post the formula you said isn't working?

Here is the formula in B14.

=SUMPRODUCT($C$2:$E$10*($C$1:$E$1=$A14)*($A$2:$A$10=B$13))

The formula works if there were nothing in the blank cells in the top table of the previously posted image. However, there is another formula in there that returns a blank value.

=IF($L7="","",1)

In the actual file, the one is replaced with coding that pulls information from another sheet. I just entered 1 to simplify the formula.