count, sumif, sumproduct

  • Thread starter Thread starter RichardO
  • Start date Start date
R

RichardO

Hi all:

I need your help with the following:

In column J, my data contains SCI, A/P, Buff, Manager, Analyst, Review
Code, e.t.c., the data could also have blank spots in this column
i.e. "".
In column I, my data contains $ amounts.
In column M, my data contains "Yes" or "No"

I want Excel to add the dollars in I when M is a yes for everything i
J EXCEPT for: SCI, A/P, Code and Blank.

I have the following formula to get the sum in I, but the number I a
getting is not correct:


=IF('Raw Data'!$J$2:$J$999<>"A/P",IF('Ra
Data'!$J$2:$J$999<>"SCI",IF('Raw Data'!$J$2:$J$999<>"Code",IF('Ra
Data'!$J$2:$J$999<>"",SUMPRODUCT(('Raw Data'!$I$2:$I$999)*('Ra
Data'!$M$2:$M$999="Yes"))))))



Thanks a lot for your kind assistance.


RichardO
 
One way

=SUMPRODUCT(--('Raw Data'!$J$2:$J$999<>"A/P"),--('Raw
Data'!$J$2:$J$999<>"SCI"),--('Raw Data'!$J$2:$J$999<>"Code"),--('Raw
Data'!$M$2:$M$999="Yes"),--('Raw Data'!$J$2:$J$999<>""),'Raw
Data'!$I$2:$I$999)
 
Back
Top