FORMULA QUESTIONS!?!?

[ellie0127]

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!

 

[Datsmart]

=SUMPRODUCT(--(A3:A100="new"),--(B3:B100="smith"))

 

[ellie0127]

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

 

[dave3009]

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]

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]

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]

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

 

[dave3009]

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]

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

 

[ellie0127]

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?