View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jacob Skaria Jacob Skaria is offline
external usenet poster
 
Posts: 8,520
Default Help in counting and summing cells based on multiple conditions

'Try the below

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*
(ISNUMBER(D1:D100)))

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1:D100)

=SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1:D100)))

If this post helps click Yes
---------------
Jacob Skaria


"Dave" wrote:

I have a spreadsheet I use for sales management and need help with 3 formulas
I cannot figure out. Following is what I am trying to do:
1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and
the value in cells C1:C100=Prime, count the cells in D1:D100 that have a
value in them. (column D contains numbers.)
2. Same as number 1 but instead of counting, sum the cells in D1:D100 that
have a value in them.
3. If the value in cells A1:A100=1, then count the cells in D1:D100 that
have a value in them. (column D contains numbers.)
Any help would be greatly appreciatied!