sumproduct again

[RichardO]

Hello All again!
Can I write a sumproduct formula if I have 3 different conditions.
I want to do the following: Count the number of YES if: column H ha
the word Anne AND column G has yesterday's date (i.e. today -
business day) AND column Q has the word "YES'"

I have the following formula, but it's not working.
=SUMPRODUCT((Workbook!$H$2:$H$999="Anne")*(Workbook!$G$2:$G$999=today-1)*(Workbook!$Q$2:$Q$999="Yes"))

First of all, I don't know how to tell excel I want today's date minu
1 business day. Second I don't know if sumproduct can handle
conditions.

Thank you for helping out.


RichardO

 

[JE McGimpsey]

SumProduct can handle up to 30 arguments. Since your're using the *
operator to multiply arrays, the multiplication takes place before
SUMPRODUCT is called, so SUMPRODUCT sees only one argument.


You can use

(Workbook!$G$2:$G$999=(TODAY()-1))

As one argument

 

[Frank Kabel]

Hi Richard
SUMPRODUCT can handle multiple conditions. Try the following
=SUMPRODUCT((Workbook!$H$2:$H$999="Anne")*(Workbook!$G$2:$G$999=WORKDAY
(TODAY(),-1))*(Workbook!$Q$2:$Q$999="Yes"))

 

[RichardO]

Hello Frank,
Thank you for helping out.

I used the following formula, but it's giving me 0, i.e. it's sayin
that there is 0 count for all these conditions. I know for th
conditions below, the answer should be 1 from my data. Do you have an
idea why it's not working?


=SUMPRODUCT((Workbook!$H$2:$H$999="Anne")*(Workbook!$G$2:$G$999=WORKDAY(TODAY(),-1))*(Workbook!$Q$2:$Q$999="Yes")*(Workbook!$O$2:$O$999=0))

Thanks.


RichardO

 

[Frank Kabel]

Hi
some ideas (though difficult to say)
- is column G really a date in your data
- column O is checked to be zero, is this correct?

You may mail me your file and I'll have a look at it
frank[dot]kabel[at]freenet[dot]de

 

[RichardO]

Hi Frank:

I think you're right about the format of the data, I have sent you
mail, please take a look at my file. Thanks for all your help.


RichardO

 

[Frank Kabel]

Hi
you column G does not store only dates but a date/time value. Try the
following formula
=SUMPRODUCT((Workbook!$H$2:$H$999="Anne")*(INT(Workbook!$G$2:$G$999)=WO
RKDAY(TODAY(),-1))*(Workbook!$Q$2:$Q$999="Yes")*(Workbook!$O$2:$O$999=0
))

This returns 1 for me

 

[RichardO]

Hi Frank,

Just a question about the formula you helped with a while back:

=SUMPRODUCT((Workbook!$H$2:$H$999="Anne")*(INT(Workbook!$G$2:$G$999)=WORKDAY(TODAY(),-1))*(Workbook!$Q$2:$Q$999="Yes")*(Workbook!$O$2:$O$999=0))


This formula works fine. But I would like to change the today() to a
cell reference instead e.g. b2 and in b2 I have a date in there e.g.
05/20/2004, but it's not working, do you know what I am doing wrong?

=SUMPRODUCT((Workbook!$H$2:$H$999="Anne")*(INT(Workbook!$G$2:$G$999)=WORKDAY(b2,-1))*(Workbook!$Q$2:$Q$999="Yes")*(Workbook!$O$2:$O$999=0))



Thanks much.


Richardo

 

[Frank Kabel]

Hi
this formula should work if B2 conatians a date. what error did you
get?

 

[RichardO]

You're right Frank. I just noticed that the cell I put in the date wa
not formated to a date, I tried formatting the cell, but it wouldn'
work, so I decided to change the cell and it's working fine.

Thanks much.


Richardo