MORE THAN ONE RECORD MEETS THE CRITERIA

[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

 

[Paul]

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

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))

 

[Maureen]

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,

 

[Jim]

More directly: =SUMIF(H7:H25,"PXG",G7:G25)

 

[Paul]

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

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,