SUMPRODUCT question

  • Thread starter Thread starter Dave Aultman
  • Start date Start date
D

Dave Aultman

I'd like to use this variation of sumproduct

=SUMPRODUCT((A1:A20="YES")*(B1:B20="NO"))

This only appears to work if the values in the ranges are text.

I can't figure a variation that works if the values are numbers. More
importantly, for what I'm trying to accomplish, I'd like to be able to
have it check against a reference cell (ie., replace "yes" with a
reference to a another cell).

My example:

I have two sheets, the first is the actual data, like this

Zone Value
913 100
913 100
913 99
913 54
914 100
914 45
914 51
915 100
915 100
915 100

On the second sheet, I just want a summary that will tell me the
summary of each zone's total number of 100's:

Zone Total 100 Scores
913 2
914 1
915 3

So I thought the formula in B1 should be:
=SUMPRODUCT((Sheet1!A1:A20=A1)*(Sheet!B1:B20=100)) Make sense, but
doesn't work.

I came across this sumproduct trick while trying to figure out what
I'm really after in the long run, which is what percentage of each
zone's scores are 100, so final result table would look like:

Zone %
913 66
914 33
915 100

Thanks for any tips.
 
How isn't it working? Are you getting an error, or are
the numbers wrong? If you're getting a REF error, the
formula isn't referencing Sheet1 correctly (note that in
your sample below, you reference "Sheet", not "Sheet1" in
the 2nd part of the formula. That could be a type here
only, tho). If your numbers aren't correct, you probably
copied the formula to other rows and the A1:A20 and
B1:B20 changed. Make those references absolute and then
copy. To get the percentage, you could make the formula:
=SUMPRODUCT((Sheet1!$A$1:$A$20=A2)*(Sheet1
$B$1:$B$20=100)), then format the resulting cell as
percent.
 
Sheet 1
Labels in Row1
Data = A2:B20

Sheet 2
Labels in Row1
A2:A? = zone numbers
ColB = Totals
ColC = Percent (format column as %)

In B2 enter:
=SUMPRODUCT((Sheet1!$A$2:$A$20=A2)*(Sheet1!$B$2:$B$20=100))

The example in your post had different numbers of entries for zones, so,
In C2 enter:
=B2/COUNTIF(Sheet1!$A$2:$A$20,A2)

Select both B2 and C2 and drag down to copy as needed.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================





I'd like to use this variation of sumproduct

=SUMPRODUCT((A1:A20="YES")*(B1:B20="NO"))

This only appears to work if the values in the ranges are text.

I can't figure a variation that works if the values are numbers. More
importantly, for what I'm trying to accomplish, I'd like to be able to
have it check against a reference cell (ie., replace "yes" with a
reference to a another cell).

My example:

I have two sheets, the first is the actual data, like this

Zone Value
913 100
913 100
913 99
913 54
914 100
914 45
914 51
915 100
915 100
915 100

On the second sheet, I just want a summary that will tell me the
summary of each zone's total number of 100's:

Zone Total 100 Scores
913 2
914 1
915 3

So I thought the formula in B1 should be:
=SUMPRODUCT((Sheet1!A1:A20=A1)*(Sheet!B1:B20=100)) Make sense, but
doesn't work.

I came across this sumproduct trick while trying to figure out what
I'm really after in the long run, which is what percentage of each
zone's scores are 100, so final result table would look like:

Zone %
913 66
914 33
915 100

Thanks for any tips.
 
Thanks for the response Peg1. I was getting either an inaccurate
count or a #NUM error, depending on how I entered the formula. Then
NUM error was when my range referred to a whole column (A:A) rather
than a range of cells. Interesting, everything seems to work as
advertised here at home on my laptop (Excel 2003), but at the office
(Excel 2000), I'm sure it was not working when the data was numbers.

The "Sheet" reference here was a typo, not my actual formulas.

Your example to get the percentage doesn't work, it just changes the
actual count to a percentage (ie, if 100 appears twice, the answer is
200%, but the correct answer should be a percentage. There needs to
be some division by the number of occurences in column A (ie.,
countif).

I'll have to retry this at work, it appears to work just fine here
now.
 
Perfect.

I'm getting off topic, perhaps, but I'm just starting to play with
Pivot Tables, and it seems like this should be a perfect pivot table
kind of thing. I could find no way to get a pivot table to give a
percentage of the counts, no matter how I formatted it.

Thanks for the help.
 
Back
Top