PDA

View Full Version : Solved: CountIF using two criteria



rajkumar
01-10-2011, 12:44 AM
Hi,

I need help in counting values by two criteria. attached sample file.

in summary sheet total number of carry forward calls for each ASP has countif formula to count total calls for that ASP.

i want if the call type is "Volume Spare" then it should not be counted against that ASP.

Example: say ASP Caravan has 23 calls (other calls + volume spare calls)
but i want to show 11 calls only since there are 12 calls on volume spare.

I heard SUMPRODUCT can do this for me, but unfortunately i do not know how to use it for my requirement.

Please help

Raj :help

Bob Phillips
01-10-2011, 01:14 AM
What is ASP? Where is it in the data?

rajkumar
01-10-2011, 01:24 AM
ASP means (column N). Authorised Service Provider (ASP)
caravan is an ASP similarly VINTEC is another ASP.

i need to exclude volume spare call type for each ASP and count number of other calls.

hope this clarifies.

Bob Phillips
01-10-2011, 01:28 AM
Column N is Customer Type in the file that you provided, and there is no Caravan that I can see.

rajkumar
01-10-2011, 01:38 AM
oops!, my mistake - i attached a wrong file
please find here is the correct one

Bob Phillips
01-10-2011, 01:58 AM
Try

=SUMPRODUCT(--('Open Calls'!$N$2:$N$100=B3),--('Open Calls'!$B$2:$B$100="Volume Spare"))

rajkumar
01-10-2011, 06:59 AM
Hi Xld,

it works for one situation only. if ASPs other than caravan have calls which are not Volume Spare are resulting to 0.

I need to exclude only volume spare calls from the list and count the rest for each ASP.
Please see the attachment.

Regards
Raj

mohanvijay
01-10-2011, 08:52 AM
just use same function with "if" like below

=IF(SUMPRODUCT(--('Open Calls'!$N$2:$N$100=B2),--('Open Calls'!$B$2:$B$100="Volume Spare"))=0,COUNTIF('Open Calls'!N:N,Summary!B2),SUMPRODUCT(--('Open Calls'!$N$2:$N$100=B2),--('Open Calls'!$B$2:$B$100="Volume Spare")))

Bob Phillips
01-10-2011, 08:59 AM
Hi Xld,

it works for one situation only. if ASPs other than caravan have calls which are not Volume Spare are resulting to 0.

I need to exclude only volume spare calls from the list and count the rest for each ASP.
Please see the attachment.

Regards
Raj

Surely, that formula does just that. By comparing to B3, you can drag-copy it down and it adjusts.

rajkumar
01-10-2011, 09:47 AM
thx xld and mohan

that's what exactly what i did. but the sum formula in the bottom row is not calculating the change.

hence re writing like this succeeded

=COUNTIF('Open Calls'!$N:$N,B2)-SUMPRODUCT(--('Open Calls'!$N$2:$N$1000=B2),--('Open Calls'!$B$2:$B$1000="Volume Spare"))

Raj

Bob Phillips
01-10-2011, 11:09 AM
So you want NOT Volume Spare

=SUMPRODUCT(--('Open Calls'!$N$2:$N$100=B2),--('Open Calls'!$B$2:$B$100<>"Volume Spare"))

dani9
04-06-2011, 05:07 AM
For lngCol = 1 To lngColMax

If WorksheetFunction.CountIf(Sheets("Settings").Range("D12:D21"), Sheets(CStr(lngTabelle)).Cells(1, lngCol)) <> 0 Then
Sheets(CStr("F" & lngTabelle)).Cells(2, lngCol).FormulaR1C1 = "=IF(ISERROR(YEAR('" & lngTabelle & "'!R[0]C[0])),'" & lngTabelle & "'!R[0]C[0],IF(YEAR('" & lngTabelle & "'!R[0]C[0])=YEAR(NOW()),CONCATENATE(DAY('" & lngTabelle & "'!R[0]C[0]),""."",MONTH('" & lngTabelle & "'!R[0]C[0])),CONCATENATE(MONTH('" & lngTabelle & "'!R[0]C[0]),""."",RIGHT(YEAR('" & lngTabelle & "'!R[0]C[0]),2))))"
Else
Sheets(CStr("F" & lngTabelle)).Cells(2, lngCol).FormulaR1C1 = "=IF(R1C<>0,INDEX('" & lngTabelle & "'!R2C1:R" & lngRowMax & "C" & lngColRaw & ",,R2C[182]),"""")"
End If

Sheets(CStr("F" & lngTabelle)).Cells(2, lngCol).AutoFill Destination:=Range(Cells(2, lngCol), Cells(lngRowMax, lngCol)), Type:=xlFillDefault
Sheets(CStr("F" & lngTabelle)).Range(Cells(2, lngCol), Cells(lngRowMax, lngCol)).Copy
Sheets(CStr("F" & lngTabelle)).Range(Cells(2, lngCol), Cells(lngRowMax, lngCol)).PasteSpecial xlPasteValuesAndNumberFormats

the problem is, that i need to change the lngCol expression in the CountIf, because it copies it from -for example - column 36 to column 36, but i have to distinct it, so it doesn´t put it on to column 36 but into an active column in Sheets(CStr("F" & lngTabelle)).Cells(2, lngCol) - which is in my case column no.23.
And if this is false, then it copies the values into right columns

so the question is:
Can i put R1C or R1C<>0 or something like that in here
CountIf(Sheets("Settings").Range("D12 : D21"), Sheets(CStr(lngTabelle)).Cells(1, lngCol)) <> 0

it keeps refusing it if i put R[0]C[0]... any ideas?