Multi If Count

[XCESIV]

Thank you in advance for your help everyone

ok here is my issue

I have to have to have 2 criterias for it to pass before the items are counted

I have 2 columns of data.
1 being a list of clients names, and 1 being if the Connection/Abandoned

Client Column is colum "F"
Connection/Abandoned is colum "J"

I need to count how many Connected and how many abandoned for each client

So if "F" equals "BPS" and "J" equals "Connected", then count

Thank you again

Warren

[teylyn]

Try this

=Sumproduct(--(F1:F100="BPS")*--(J1:J100="Connected))

[daddylonglegs]

Try this

=Sumproduct(--(F1:F100="BPS")*--(J1:J100="Connected))

Although this will give the correct answer I wouldn't recommend this syntax.
If you have * then you don't need any of the --s, i.e. this is sufficient

=Sumproduct((F1:F100="BPS")*(J1:J100="Connected))

although, apparently more efficient is

=Sumproduct(--(F1:F100="BPS"),--(J1:J100="Connected))

Note: comma instead of *

[XCESIV]

=SUMPRODUCT(--('Wk (1)'!F:F="BPS")*--('Wk (1)'!J:J="Connected"))

This didnt work.

Came up with #NUM! error

[XCESIV]

i just worked out it cant use the F:F it needs to be F1:F10000

Worked out... thank you

[teylyn]

Sumproduct does not work with whole columns in 2003. You need to specify the rows.

[XCESIV]

thank you

Can i ask is there anywhere to get good information on how the sumproduct works.

I dont have a clue what the difference between ur 2 answers are since they both work ou tto be the same answer

I need to do another sumproduct now and still lost.

Sumproduct is very new to me and im still trying to learn how they actually work.

Here is my next issue

Column F is my Client listing
Column P Is the call type
Column I is the call duration

I need to work calculate the total duration of call time from this data for each client and each call type

So if F equals "BPS" and P equals "General" then add the value in columb I

[teylyn]

Learn more about sumproduct here

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

The formula you're looking for is

=Sumproduct((F1:F100="BPS")*(P1:P100="General")*(I1:I100))

[XCESIV]

=SUMPRODUCT(('Wk (1)'!F1:F10000="BPS")*('Wk (1)'!P1:P10000="BPS=General Enquiries/Operator;")*('Wk (1)'!I1:I10000))

this makes an error of #VALUE!

[teylyn]

=SUMPRODUCT(('Wk (1)'!F1:F10000="BPS")*('Wk (1)'!P1:P10000="BPS=General Enquiries/Operator;")*('Wk (1)'!I1:I10000))

Is this an existing value in the column?

If you count the occurences of BPS and General enquiries like this:

=SUMPRODUCT(('Wk (1)'!F1:F10000="BPS")*('Wk (1)'!P1:P10000="BPS=General Enquiries/Operator;"))

do you get an error, too??

[daddylonglegs]

=SUMPRODUCT(('Wk (1)'!F1:F10000="BPS")*('Wk (1)'!P1:P10000="BPS=General Enquiries/Operator;")*('Wk (1)'!I1:I10000))

this makes an error of #VALUE!

This is maybe where the different syntaxes become important, using * you are multiplying arrays, one of those arrays is the "sum range" 'Wk (1)'!I1:I10000. If there is any text anywhere in that range, e.g. a header in row 1....or "formula balnks" anywhere then you'll get a #VALUE error, what do you get if you change to this formula, note comma rather than *

=SUMPRODUCT(('Wk (1)'!F1:F10000="BPS")*('Wk (1)'!P1:P10000="BPS=General Enquiries/Operator;"),'Wk (1)'!I1:I10000)

Edit: Sorry XCESIV, I just edited your last post there by accident , I was attempting to "quote" you and hit the wrong button, I think I've restored it to the original......

[XCESIV]

"BPS=General Enquiries/Operator;" is the code the report builds for what i need to query
"BPS" is the account code for the client

=SUMPRODUCT(('Wk (1)'!F1:F10000="BPS")*('Wk (1)'!P1:P10000="BPS=General Enquiries/Operator;"))

gives me a a value of 1272:00:00 which is wrong
manually calculated is 3:29:45

[teylyn]

can you post a sample of your data in a file attachment please?

[XCESIV]

=SUMPRODUCT(('Wk (1)'!F1:F10000="BPS")*('Wk (1)'!P1:P10000="BPS=General Enquiries/Operator;"),'Wk (1)'!I1:I10000)

Thank you

this worked.