If/Sumproduct Combo

[day92]

Is it possible to do a IF/Sumproduct statement?

Im trying to pull data from a sheet for a certain Dr in a certain month for a certain vendor.

Here is the formula I tried but it is not working. Attached is the sheet im using. The formula is in B13 to B21. It should only return values for two of the vendors which are highlighted.

=IF(Database!A:A=Gray!B5,SUMPRODUCT((Database!$B:$B=A13)*(Database!$G:$G=$B$5),(Database!$C:$C)),"")

[day92]

Figured it out.

[TMS]

Just use SUMPRODUCT

See the attached updated spreadsheet.


Edit: I would recommend that you do NOT use SUMPRODUCT on entire columns although it is "acceptable" in Excel 2007/2010


Regards

[Roger Govier]

HI

Firstly, although XL2007 and XL2010 will take a full column as a range in Sumproduct formulae - it is to be avoided.
With over 1 million rows in a column, all SP formulae will become very slow.

Instead, create a Table as I have in the attached workbook
Delete all your blank rows in the data base.
Place cursor within table >Insert tab>Table>my data has headers.
The size of he table will grow dynamically as you add more data.

Then you can refer to the table header name in the formulae

CODE
in cell B13
=SUMPRODUCT((Table1[Vendor Name]=$A13)*(Table1[MONTH]=B$5)*(Table1[Doctor]=$B$1),(Table1[PO Amount]))

and copy down with the fill handle.
Then copy B13:B1, select C13:M13 and Paste Special>Formulas
(You cannot drag fill table formulae across the page.)

IN cell B6
=SUMPRODUCT((Table1[MONTH]=B$5)*(Table1[Doctor]=$B$1),(Table1[PO Amount]))
copy formula across

In cell B7
=IFERROR(AVERAGEIF(Table1[MONTH],Jafari!B$5,Table1[PO Amount]),0)

I also created a unique list of Doctors on sheet Database, and set up Data Validation in cell B1 of sheet Jafari to enable you to easily select a different doctor to see his results.

[day92]

Roger - thanks for the information, I will give that a shot. It did start to run a little slow so that may be the way to go.

Thanks again!