Using Sumproduct on blank cells

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
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.
 

Attachments

  • Sumproduct Format.PNG
    Sumproduct Format.PNG
    15.6 KB · Views: 4

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
What is the formula you are using for that example?
 

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Why not use sumproduct?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Can you just post the formula you said isn't working?
 

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
Can you just post the formula you said isn't working?
Here is the formula in B14.

Excel Formula:
=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.

Excel Formula:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,263
Messages
5,600,590
Members
414,391
Latest member
Pandurang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top