Getting a Sum in 1 Column Based on Criteria from 2 other columns.

[tkaye]

I have a spreadsheet with 5 columns

Training Type Title Requirement Points Notes


I would like to create a formula that calculates how many points a person with a particular employee title could accumulate if they took the courses that contained the words: Suggested and Required in the column: Requirement. (i.e., how many points could a Superintendent get if he took all the courses that were either required of his title or suggested for his title). I have enclosed a copy of my worksheet).

[VBA Noob]

Maybe

=SUMPRODUCT((B2:B260="SUPERINTENDENT")*(C2:C260={"Suggested","Required"})*(D2:D260))

VBA Noob

[tkaye]

It worked, but I had to expand on it just a little to capture all my requireds and suggesteds. Is there a way to throw a wildcard in there?

=SUMPRODUCT((B2:B260="SUPERINTENDENT")*(C2:C260={"REQUIRED","SUGGESTED","SUGGESTED (IF ON MD JOB)","SUGGESTED (IF ON VA JOB)","REQUIRED (IF ON VA JOB)","REQUIRED (IF ON MD JOB)","REQUIRED IF NEW"})*(D2:D260))

tkaye

[VBA Noob]

Maybe

=SUMPRODUCT((B2:B260="SUPERINTENDENT")*(ISNUMBER(SEARCH({"Suggested","Required"},C2:C260))*(D2:D260)))

VBA Noob

[tkaye]

Yup that worked! Thank you so much for the fast assistance!!