SumProduct Question

[Anne]

I am trying to sum amounts based on two conditions. The information below is
a sample of what my spreadsheet looks like:

Line Groupings Data
Per1

Line 11 - 1# 1# Bottle Total Shifts Available
528
Line 11 - 1# 1# Bottle Total Shifts Required
69
Line 11 - 1# 1# Bottle Total Shifts Remaining -
Constrained 459
Line 14 - 2# FS 2# FS Bottle (Ln14) Total Shifts Available
528
Line 14 - 2# FS 2# FS Bottle (Ln14) Total Shifts Required
25
Line 14 - 2# FS 2# FS Bottle (Ln14) Total Shifts Remaining -
Constrained 503


I would like to sum the amounts from Per1 (Period 1) based on Grouping and
Data. I will also be using this formula to add more columns such as Per2 &
Per3 that are in columns E and F.

I have tried the following formula, but am not having success.

=SUMPRODUCT(--($B$2:$B$239=B2,--($C$2:$C$239="Total Shifts
Required"),$D$2:$D$239)


Any help would be appreciated.

Thanks,

Anne

 

[Anne]

I noticed the data did not copy over very well. Per1 should be in column D.
and the numbers such as 528 and 69 are also in column D.

 

[~L]

=SUMPRODUCT(--($B$2:$B$239=B2,--($C$2:$C$239="Total Shifts

Required"),$D$2:$D$239)

I see a missing ).

=SUMPRODUCT(--($B$2:$B$239=B2),--($C$2:$C$239="Total Shifts
Required"),$D$2:$D$239)

If that was a typo rather than what's wrong with the formula, I see quite a
bit of text in your data so you might sub TRIM($C$2:$C$239) for just the
range. The other usual suspect is numbers stored as text, so check PER1 (and
2 and 3) for format. Aside from those details, it seems you constructed the
right formula for what you describe.

What sort of error are you receiving?

 

[Anne]

Thanks for the help. I'm one step closer, but I am now getting a zero value.

 

[~L]

I'm still leaning toward text issue.

This seems to be a formula you are going to create an entire column out of,
are all of the rows in that column 0?

If not, to troubleshoot, try using the advanced filter tool to create lists
of unique values in an unused column off to the side for column B and column
C. If the same values appear to appear twice, you may have to clean up the
text entries.

If all of the values are 0, it means there are no matches. Instead of
having "total shifts required" in the formula, copy one of the cells that
contains that value to another location (we'll call it G1) and sub $G$1 into
the formula where there was text before. Now we're sure we have at least one
match (on the row where you copied the text from) because B(whatever that row
number was) will always be equal to itself even if there is a typo somewhere,
and the text you copied will be equal to itself, so unless the number for
that row is 0, something else must be wrong.

In that case, check to make sure calculation for your spreadsheet is set to
automatic instead of manual (hitting F9 would work unless...), and that
calculation is not disabled by code (Alt F11 to access the VB editor).

 

[Anne]

I finally got it to work. Thank you so much for your help. Text was the
issue.

 

[~L]

Glad I could help.

I finally got it to work. Thank you so much for your help. Text was the
issue.