Solved: CountIF using two criteria

[rajkumar]

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

[Bob Phillips]

What is ASP? Where is it in the data?

[rajkumar]

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]

Column N is Customer Type in the file that you provided, and there is no Caravan that I can see.

[rajkumar]

oops!, my mistake - i attached a wrong file
please find here is the correct one

[Bob Phillips]

Try

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

[rajkumar]

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]

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]

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]

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

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

[Bob Phillips]

So you want NOT Volume Spare

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

[dani9]

[VBA]
For lngCol = 1 To lngColMax

If WorksheetFunction.CountIf(Sheets("Settings").Range("D1221"), 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[/VBA]

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?