Sumproduct with 2 variables in 2 columns

**nynemout42** · 10-17-2012, 01:27 AM

OK guys...I thought this would be simple, but I was definitely wrong. I am trying to use the sumproduct formula and it continues to give me an error. I have two columns. The first column displays completed, cancelled or pending. This column is a drop down menu when entering the data. The second column is a drop down menu of hospital names that were the destination of our calls. I need a count of completed calls to a specific hospital. Example: I need the total number of calls that show completed with the destination of Security Forces Hospital- Riyadh.

Here is the formula I am entering: =SUMPRODUCT((g27:g348="completed")*(L27:L348="Security Forces Hospital- Riyadh")). I have even tried used the shift+ctrl+enter function. I have checked the spelling multiple times as well as the columns and rows for accuracy. Initially, I was getting a #name error, but I have tried this formula so many ways that I don't remember what that particular formula was. Now all I am getting is the typical "The formula you typed contains an error" Excel pop up window. Could it be the drop down menu in the G column and the L column that is confusing the formula?

Thanks in advance for the help!!!

Jeremy

**twiggywales** · 10-17-2012, 03:50 AM

you were close but i think you need to do this

=SUMPRODUCT(--(g27:g348="completed")*--(L27:L348="Security Forces Hospital- Riyadh"))

that should give you a count of where both criteria are met

here is an example book showing how to use the sumproduct function in this way

SUMPRODUCT Example 1.xlsx

**NBVC** · 10-17-2012, 07:53 AM

The formula, as you have it, should work without telling you ""The formula you typed contains an error". You don't need the -- and the * , one is redundant to the other.

If you are getting the #VALUE error, it means you have a #VALUE error somewhere in the data, check for that.

If you are getting the #N/A error, it means you might have #N/A error somewhere in the data or you may have range sizes that are not equal.

If still an issue, maybe post a sample workbook showing the problem.

**nynemout42** · 10-18-2012, 01:41 AM

OK...the formula as written by Twiggywales worked! It gave me the same number as I had counted by hand. Don't know if it makes a difference as a formula is a formula, but I am working on a Mac. I'm sure glad there are people out there that have a better grasp on this than I do! You guys have saved me hours of work!

Thanks, Thanks, Thanks!!!

**vlady** · 10-18-2012, 04:08 AM

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".
For the meantime I'll do it for you.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.