FORMULA QUESTIONS!?!?

ellie0127

New Member
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!

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Code:
=SUMPRODUCT(--(A3:A100="new"),--(B3:B100="smith"))

Entering =SUMPRODUCT(--(A3:A100="new"),--(B3:B100="smith")) as suggested, returns an Array error.

Not sure if this is correct but I think Conditional Sum Add-in needs to be activated to use SUMPRODUCT

HTH

Dave

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.

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

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

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

forgot to add the absolute \$ references help especially if your looking to copy it down to change the ="value" or the arrays.

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?

Replies
5
Views
178
Replies
2
Views
208
Replies
9
Views
228
Replies
2
Views
315
Replies
5
Views
474

1,219,570
Messages
6,149,044
Members
450,853
Latest member
xtiinctt

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.

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

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