I, too, am having problems with SUMPRODUCT

[Guest]

I assume SUMPRODUCT is what I want.

In sheeta, I have to columns of interest:
A2:A127 is text data created by IF statements. An example of A2 would be
"0-30", meaning to say "0-30 days". B2:B127 is a list of last names, also
created by a worksheet function.

In sheet b, I'm creating tables based on these, and other data elelments.

In one table, I do a =COUNTIF('sheeta'!A2-A127,"=" &'sheetb'!F2) sheetb F2
is a text colum that says "0-30" THis works fine.

I want to do the same exact function, but check column B2:B127 in sheet a
for a name. SUMPRODUCT I tried was:

In sheetb, Cell G10 I have the following:
=SUMPRODUCT('sheeta'!B2:B127=$G$9)*SUMPRODUCT('sheetb'!A2-A127=F10)
(G9 is a name, such as Smith, which occurs on various lines in sheeta B2:B127(
F10 is just like F2 above, it has the text, "0-30".

What I want is a total number for each row that has both "Smith" in the B
column and "0-30" in the A-column.

(Buy the way I have a PIVOT table that does this just fine, but I need
something that is dynamically upadated (when the data changes).

Thanks!

KSL.

 

[krcowen]

KSL

You need to check the syntax of sumproduct. Try

=SUMPRODUCT(--('sheeta'!B2:B127=$G$9),--('sheetb'!A2-A127=F10))

Good luck.

Ken

 

[Guest]

Thanks for the prompt resonse!

Unfortunately, that gives me a total of:
(# of occurrances of "name") * (# of occurances of "0-30")

I think I'm trying the wrong approach.

I'm trying to find out how many times "Name($G$9)" and "0-30 (F10)" occur on
the same line.

Any ideas?

KSL

 

[Peo Sjoblom]

No it does not, it returns the number of occurrences where the name and 0-30
occur on the same row.

 

[Guest]

That's not what I'm seeing, I must have it wrong.

Here's what I have

F G H I J
9 Age of SR Leonhardt Smith Jones TOTAL
10 0-29 279 2 1 9
11 30-59
12 60-89
13 90-120

in G10, my formula is:
=SUMPRODUCT(--('sheeta'!B2:B127=$G$9)*SUMPRODUCT(--('sheeta'!A2:A127=F10)))

The first have of the forumla is 31 (which is true), the second half of the
formula is 9, which is also true. Multiplied together they are 279.

Manually counting, there should only be 6 in G10.

What am I doing wrong?

KSL.

 

[krcowen]

KSL

The problem is that you are using the formula you have in G10 and not
the one Peo and I have been trying to get you to use. Try using
something like:

=SUMPRODUCT(--('sheeta'!B2:B127=$G$9),--('sheetb'!A2-A127=F10))

It appears you are using two sumproducts rather than one, and it is
doing exactly what you say it is doing. If you use one sumproduct (as
shown above) and inside the one sumproduct you multiply the two
strings of Trues and Falses you will get what you want.

Good luck.

Ken