Sumproduct, text label and errors...

[Zeitlin]

Folks,

I have the following formula:

=SUMPRODUCT(('SeaTask Detail'!H7:H31='Budget Detail'!E3)*('SeaTask
Detail'!I7:I31='Budget Detail'!G3)*('SeaTask Detail'!R7:R31))

What I am trying to do, is based on two conditions, determine the
amount of money allocated to a particular budget.

The problem I have is that the 'seatask detail' is about 50 different
report breakouts on one sheet.

Simple example:

Account: East
Who: Joe Blow

Budget Line Item Amount
001 A $50
002 B $75

Account: West
Who: Joe Blow

Budget Line Item Amount
001 A $53
002 B $33

So, basically I am trying to find the total for all budgets 001, line
item A (in this case $103 dollars).

The problem I am having is that those darn titles in each set of
reports is resulting in my sumproduct formula returninga #value. If I
get rid of those headings it works fine.

Help! How do I work around this problem?

Thanks for your time!!

v/r

Paul Zeitlin

 

[Kevin Stecyk]

Hi Paul,

You wrote, <<If I get rid of those headings it works fine.>>

Can you make a duplicate of your data. And delete the headings on one copy
and use your formula?

If so, here is how you can readily get rid of the headings.

Select the column of data where you have numbers (say the dollar amounts)
and name that block of cells "NumberCol".

Then use this macro to delete the rows where there are no numbers. Now you
are just left with your data, and your formula should work.

~~~~~~~~~~~~~~~~~~~~~~~~
Sub DelTitleRows()

Dim rnRng As Range
Dim rnCell As Range


Set rnRng = Range("NumberCol")

For Each rnCell In rnRng
If Not Application.WorksheetFunction.IsNumber(rnCell) Then
rnCell.EntireRow.Delete
End If
Next

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~

You still have your original data with all the column headings.

Regards,
Kevin

 

[Harlan Grove]

I have the following formula:

=SUMPRODUCT(('SeaTask Detail'!H7:H31='Budget Detail'!E3)
*('SeaTask Detail'!I7:I31='Budget Detail'!G3)*('SeaTask Detail'!R7:R31)) ...
The problem I have is that the 'seatask detail' is about 50 different
report breakouts on one sheet. ...
The problem I am having is that those darn titles in each set of
reports is resulting in my sumproduct formula returninga #value. If I
get rid of those headings it works fine.

...

The problem comes from the final term only, 'SeaTask Detail'!R7:R31. That can be
overcome by making it a separate argument to SUMPRODUCT. So try

=SUMPRODUCT(('SeaTask Detail'!H7:H31='Budget Detail'!E3)
*('SeaTask Detail'!I7:I31='Budget Detail'!G3),('SeaTask Detail'!R7:R31))

(the last asterisk replaced with a comma).