MORE THAN ONE RECORD MEETS THE CRITERIA

  • Thread starter Thread starter Maureen
  • Start date Start date
M

Maureen

I'm using the DSUM function to obtain info from a database
using a criteria range named "Price_A" as the criteria to
locate items coded "PXG". This produces a result that
contains these items and others coded "PXGA" located in a
criteria range named "Price_B". How can I limit the result
to the first criteria range?

Thanks
 
Maureen said:
I'm using the DSUM function to obtain info from a database
using a criteria range named "Price_A" as the criteria to
locate items coded "PXG". This produces a result that
contains these items and others coded "PXGA" located in a
criteria range named "Price_B". How can I limit the result
to the first criteria range?

Thanks
Click to expand...

That is one of the many limitations of the database functions, which were
only included in Excel in the first place for compatibility with other
spreadsheet programs. You would do better using Excel's native function
SUMPRODUCT. You don't need a criteria range for this, just something like:
=SUMPRODUCT((A1:A100="PXG")*(B1:B100))
 
I'm not familiar with SUMPRODUCT. If the range
containing "PXG" is located in H7:H25 and the values I
need are located in G7:G25, how do I work this into the
SUMPRODUCT formula?

Thanks,
 
=SUMPRODUCT((H7:H25="PXG")*(G7:G25))

Maureen said:
I'm not familiar with SUMPRODUCT. If the range
containing "PXG" is located in H7:H25 and the values I
need are located in G7:G25, how do I work this into the
SUMPRODUCT formula?

Thanks,
Click to expand...
 
Back
Top