I cannot perform this function PLEASE HELP!

[Sunny]

Hi Im trying to combine an IF statement with a COUNTIF statement, the fuction
is below:
=COUNTIF('Events Due August'!F2:'Events Due August'!F1206,'Customer Data
Sheet'!B7)*IF('Events Due August'!B:B,'Customer Data Sheet'!C3:E3)

So basiclly its trying to look up the total count of a column with a certain
event description for a certain customer. Its not working what am I doing
wrong??

Can someone let me know, thanks!

Sunny

 

[Bob Phillips]

Your formula attempt doesn't quite make sense to me, you are checking a
range against a range in your IF,but it would be something like

=SUMPRODUCT(--('Events Due August'!F2:F1206='Customer Data Sheet'!B7),
--('Events Due August'!B2:B1206,'Customer Data
Sheet'!C3))

Note that SUMPRODUCT doesn't work with complete columns prior to XL2007, you
have to specify a range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sunny]

Hi Bob, thanks alot for your help, I have tried the function and played
around with it abit but still no luck, the part below works fine:

=SUMPRODUCT(--('Events Due August'!F2:F1206='Customer Data Sheet'!B7)

This works fine and shows me what I require which is the total number of
events due which are = to the even description 'safety inspection' which is
in Column B7. However the next part which I require in the same formula is
only showing the results with a specific customer name. So I'm looking for
the total number of events for a specific event description e.g safety
inspection for a specific customer which is where the C3 in the customer data
sheet comes in on my original formula. Its just this last part where I can
filter the end result by customer name which is causing me difficulties as
the rest of the formula works. What shall I do? if it help can I show you
this file by e-mail? I can put my e-mail address up on here for you? I would
really appreciate it, let me know, thanks!

Sunny


=SUMPRODUCT(--('Events Due August'!F2:F1206='Customer Data Sheet'!B7)

 

[Bob Phillips]

Sorry, I made a mess of the formula, I really meant

=SUMPRODUCT(--('Events Due August'!F2:F1206='Customer Data Sheet'!B7),
--('Events Due August'!B2:B1206='Customer Data
Sheet'!C3))

See if that is any better.

On the first part, a simple

=COUNTIF('Events Due August'!F2:F1206,'Customer Data Sheet'!B7)

is a better solution



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sunny]

Hi, it still doesnt work would it be possible for me to show you the
actual spreadsheet via e-mail, you would prob notice the problem immediately
i just cant quiet figure it out.. let me know, thanks..

 

[Bob Phillips]

Okay, my email address is in my headers, just a bit obfuscated as stated in
my signature.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)