List Results Based on Multiple Criteria

**scottcnichols** · 01-22-2013, 03:11 PM

Hello all. I'm new to this forum. I am trying to list position numbers that meet multiple criteria. I am currently using Sumproduct to count them but I also need to list the results. the sumproduct formula is =SUMPRODUCT((A:A>1)*(C:C=52280)*(W:W=1)) Where A:A is the Position Count (where the number of employees in the same position number is more than 1), C:C is the Department Number and W:W is maximum head count for a position number. I'm trying to list the position numbers (B:B) that meet those criteria.

Thank you in advance!

**FDibbins** · 01-22-2013, 03:21 PM

Hi scott and welcome to the forum

perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**scottcnichols** · 01-22-2013, 04:40 PM

Thank you FDibbins. Attached is a file. I removed most of the other data so the column letters are different than the original post.

Thank you again!

**FDibbins** · 01-22-2013, 06:12 PM

thanks for the file

This may be too simplistic, but have you considered using filters? you already have them applied, just un-select what you dont want to see, column-by-column, and you will end up with the list you want?

**scottcnichols** · 01-22-2013, 06:38 PM

Haha! Good call. I am using the filters to check the results of the formulas. I will be sending this report to about 15 different Department heads (each with a different Department Number) so I will need different results for different Department Numbers. This sample is just one of four different sets of criteria I am looking for from the same dataset so I'm hoping to have some automation in report. Once I get the formula to work I can change the variables to meet each department's criteria. I hope that makes sense...

**scottcnichols** · 01-22-2013, 07:51 PM

Here is another version of my spreadsheet with a more complete version of the data. The StaffingData Tab is my main data (this is downloaded from a query from our Payroll database). The ITSD and Admin tabs are samples of 2 of the 15 departments I will send reports to. I am trying to keep the reports simple for the department heads to review. But the goal is to run the payroll query at the beginning of each month and have these cells updated automatically.

Thank you again for your help...

**FDibbins** · 01-23-2013, 01:15 AM

It would make it easier to understand if the headings in sheet2 bore any relation to those in sheet1

and on a side note, all of your sumproduct functions can be done with a much simpler countifS() function...
=+SUMPRODUCT((StaffingData!C:C="52280")*(StaffingData!F:F<>"V"))
=COUNTIFS(StaffingData!C:C,$A$2,StaffingData!F:F,"<>V")

=+SUMPRODUCT((StaffingData!C:C="52280")*(StaffingData!F:F<>"V")*(StaffingData!A:A>1))
=COUNTIFS(StaffingData!C:C,"52280",StaffingData!F:F,"<>V",StaffingData!A:A,">"&1)

**scottcnichols** · 01-23-2013, 12:30 PM

Thank you so much. I appreciate the help. Is there a way to list the position numbers that meet those criteria?

List Results Based on Multiple Criteria

LinkBack

Thread Tools

Rate This Thread

Display

List Results Based on Multiple Criteria

Re: List Results Based on Multiple Criteria

Re: List Results Based on Multiple Criteria

Re: List Results Based on Multiple Criteria

Re: List Results Based on Multiple Criteria

Re: List Results Based on Multiple Criteria

Re: List Results Based on Multiple Criteria

Re: List Results Based on Multiple Criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions