FORMULA QUESTIONS!?!?

ellie0127

New Member
Joined
Sep 13, 2006
Messages
14
I am trying to write a formula that adds the number of new programs submitted by a particular person. Example.

If column B3:B100="smith", then I want to sum A3:a100 if it equals "new".

I can't figure out the formula without entering to many exceptions. Any suggestions?

Thanks!

:oops:
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

ellie0127

New Member
Joined
Sep 13, 2006
Messages
14
Entering =SUMPRODUCT(--(A3:A100="new"),--(B3:B100="smith")) as suggested, returns an Array error.
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,005
Office Version
  1. 365
  2. 2016
Not sure if this is correct but I think Conditional Sum Add-in needs to be activated to use SUMPRODUCT

Tools>>Add-ins>>Conditional sum.


HTH


Dave
 

ellie0127

New Member
Joined
Sep 13, 2006
Messages
14

ADVERTISEMENT

That does not seem to be working either. Maybe I just have to many conditions?? Here is what my current formula looks like. (keep in mind that I'm pulling information from a source sheet and entering it onto my destination sheet).

=SUM(IF('[BLUE SKY.xls]SEPT'!B3:B1000="SMITH"+('[BLUE SKY.xls]SEPT'!A3:A1000="N"),1,0))

This formula produces a #value! error.
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,005
Office Version
  1. 365
  2. 2016
OK Try this while BLUE SKY.xls is open.

=SUMPRODUCT(--([blue sky]'sept'!A1:A100="N"),--([blue sky]'sept'!B1:B100="SMITH"))

The way I got it to work was start with destination file open starting formula =SUMPRODUCT(--( then clicking on blue sky book the highlighting range for first array the ="N"),--(Repeat for second array)) etc.. The ,-- between arrays needs to be there.

HTH


Dave
 

ellie0127

New Member
Joined
Sep 13, 2006
Messages
14

ADVERTISEMENT

I TRIED THAT.. IT STILL DOESN'T WORK. =(
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,005
Office Version
  1. 365
  2. 2016
Can you post back your formula please.

I had this trial in Book2 looking at a small trial database on Book1 Sheet dave and it worked fine

=SUMPRODUCT(--([Book1]dave!$I$6:$I$8="smith"),--([Book1]dave!$J$6:$J$8="new"))

If I closed down Book2 (after saving) the path directory would be added to it.

KR


Dave
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,005
Office Version
  1. 365
  2. 2016
forgot to add the absolute $ references help especially if your looking to copy it down to change the ="value" or the arrays.
 

ellie0127

New Member
Joined
Sep 13, 2006
Messages
14
That worked! Now the last step is to sum revenue levels in another column if both the conditions above are met.

so if A:A="SMITH" and B:B="NEW" THEN SUM C:C.

Any ideas how that would work?
 

Forum statistics

Threads
1,136,704
Messages
5,677,297
Members
419,684
Latest member
BOB101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top