Sumproduct (two conditions)

[GerryK]

Hi,
My spreadsheet formula has lost it's way!
I would appreciate any help in determining why.

My problem:
Sheet2 A10 has 10 in it and H2 has =A10 in it.
Sheet2 F2:F10 has 10,30,40,50,10,10,10,10,10 in them.
Sheet2 G2:G10 has the text ACC in them.

Sheet1 B1 has:
=SUMPRODUCT((Sheet2!H2:H10=A10)*(Sheet2!
G2:G10="ACC"),Sheet2!F2:F10)
and is returning 170. This was anticipated to be 180 as
the next formula (my test)shows.

=SUM(Sheet2!F2:Sheet2!F10) is returning 180

What am I doing wrong?

TIA
Gerry

 

[Bob Phillips]

Gerry,.

I get 180.

Check all of the data. Most likely cause is that one of the ACCs will have
an extra space.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[GerryK]

Hi Bob,
I still cannot get 180.
Trace precedents shows cell A10 from the sheet with the
formula and it should be from the other sheet. Is there a
better way of writing that part?
The (Sheet2!H2:H10=A10) part of the first formula seems to
be my problem.
I ried your suggestion and reapplied the text ACC.

Any help is appreciated.

 

[GerryK]

Got it!
Thanks

-----Original Message-----
Hi Bob,
I still cannot get 180.
Trace precedents shows cell A10 from the sheet with the
formula and it should be from the other sheet. Is there a
better way of writing that part?
The (Sheet2!H2:H10=A10) part of the first formula seems to
be my problem.
I ried your suggestion and reapplied the text ACC.

Any help is appreciated.

.