# Microsoft Office Application Help - Excel Help forum > Excel General >  >  How to use SUMPRODUCT with multiple criteria

## missyreiber

I am stuck - I have a large amount of data for a group of physicians I work for.  I am trying to set up a monthly trend report to be able to run quickly after I plug in the data.  I want to use some sort of lookup to look up two things - 1) the physician's specialty and 2) the month.  

Can anyone look at the attached example and tell me how to do this?  I have started a SUMPRODUCT formula, but am stuck on how to tell it to find only that month's data.  

Thanks!!

----------


## NBVC

Try, in C10,

=SUMPRODUCT((data!$A$5:$A$110=$A4)*(data!$B$4:$N$4=C$9)*(data!$B$5:$N$110))

copied across

----------


## Paul

Hi Missy,

Will this work for you in cell C10:

*=SUMIF(data!$A:$A,$A$4,data!B:B)*

You can fill that formula across to O10 and it should update by month.

----------


## NBVC

Paul's is a better solution if your months are lined up the same in both sheets per your sample.

Mine allows more flexibility if you are going to play around and not show months in same order (or show all months) per your sample.  It will match exact month to month

----------


## missyreiber

NBVC - this solution worked great in my example, however, when i tried to copy it to my actual file it didn't work.  i have my lookup data and my trend sheet as two different workbooks and it won't work, but when i copy my data into the same workbook it works.  problem is - i have way too much data and need all my data files to be located in a seperate file.  do you know why this is?

----------


## NBVC

The Sumif formula won't work if you are referring to closed workbooks, whereas Sumproduct does work.  Which are you using.

----------


## missyreiber

I am using SUMPRODUCT. I need to use that because my month will be changing.

----------


## NBVC

Well, I am off now... so can't give much more immediate help... perhaps someone else can take over and assist you....

You should indicate "how it's not working" - i.e wrong numbers, error messages, etc..

----------


## missyreiber

I get the error message "#VALUE!"

----------


## NBVC

Check that there is no #Value error in your data and also try replacing last * in the formula with a comma...

=SUMPRODUCT((data!$A$5:$A$110=$A4)*(data!$B$4:$N$4=C$9),(data!$B$5:$N$110))

----------


## missyreiber

Yay - I'm SO happy and thankful!!!!  You guys are so smart!!!

----------


## amritmt

=SUMPRODUCT((Sheet4!B5:F5=Sheet1!D13)*(Sheet4!A6:A10=Sheet1!D14)*(Sheet4!B6:F10))

The above formula is working nice for sheet2 ...
how can extend this formula for 115 sheets......
 I m looking for Density at 15 Deg corresponding  to Density at natural ...
Temperature in Rows and Density in columns.............


thnkx in Advance

AMRIT

----------

