COUNT IF Hi, i'm building an app for the inv...

[Mark_Pathfinder]

COUNT IF

Hi, i’m building an app for the invoices of phone calls and i need to count all calls for a customers, for a period and for a tipe of Calls. I have 2 tables CALLS INVOICES In invoices i need to: COUNT N° OF NATIONAL CALLS for this MONTH COUNT N° OF INTERNATIONAL CALLS for this MONTH COUNT N° OF CELL CALLS for this MONTH and SUM SECONDS FOR EVERY KIND OF CALLS for this MONTH and SUM ammount OF INTERNATIONAL CALLS for this MONTH

Actually in my excel i’v inserted a complicate formula FOR EVERY CELLS

=SUMIFS(CALLS-SHEET;">=“START PERIOD;CCALLS-SHEET”<=END PERDIO;COLUMN IN CALLS SHEET;KIND OF CALLS;COLUMN CUSTOMERS IN CALLS SHEET;name of customer)

eg: (SUMIFS(CALLS SHEET;1/1/209;CALLS SHEET;28/2/2019;KIND OF CALLS;NATIONAL CALLS;CUSTOMERS CALLS;MC DOWELL LTD)

The google sheet check : THE PERIOD for The customer i chose, and calculate the numbers of calls for EVERY KIND OF CALLS

how can i do it? +Steve Coile @Aleksi_Alkio

[Steven_Coile]

For “COUNT N° OF NATIONAL CALLS for this MONTH”:

COUNT(

FILTER(

“CALLS”,

AND(

([Customer] = [_THISROW].[Customer]),

([Call Date] >= [_THISROW].[Period Begin),

([Call Date] <= [_THISROW].[Period End),

([Call Type] = “NATIONAL CALL”)

)

) )

Repeat the above as desired replacing NATIONAL CALL with whatever other call type you want a count of.

For “SUM SECONDS FOR EVERY KIND OF CALLS for this MONTH”:

SUM(

SELECT(

CALLS[SECONDS],

AND(

([Customer] = [_THISROW].[Customer]),

([Call Date] >= [_THISROW].[Period Begin),

([Call Date] <= [_THISROW].[Period End)

)

) )

For “SUM ammount OF INTERNATIONAL CALLS for this MONTH” (assuming “ammount” means seconds):

SUM(

SELECT(

CALLS[SECONDS],

AND(

([Customer] = [_THISROW].[Customer]),

([Call Date] >= [_THISROW].[Period Begin),

([Call Date] <= [_THISROW].[Period End),

([Call Type] = “INTERNATIONAL CALL”)

)

) )

[Mark_Pathfinder]

Great… five stars to you… .IT WORKS

Thanks a lot

I take advantage of your availability and super professionalism to help me solve a problem related to spreadsheet formulas

[Mark_Pathfinder]

+Steve Coile Hi Steve, i’ve last question about Sum of Seconds, i would like to convert it in TIME hh:mm:ss,

i tried to change in time the column but it says error, than i tried to have another columns with [SECONDS] and time type but it says error because is NUMBER, the last choise i’ve inserted a spreadsheet formula to copy Second Column but it doesn’t work. How can i resolve it?

[Steven_Coile]

@Mark_Pathfinder Check out the Duration from Decimal Hours example in the doc for TIME() (attached). The process for converting seconds to Duration is similar. TIME() help.appsheet.com

[Mark_Pathfinder]

Hi Steve, i’ve last question about COUNF FILTER AND

How can i do iF I’ve different call type ? i tried;

SUM(

SELECT(

CALLS[SECONDS],

AND(

([Customer] = [_THISROW].[Customer]),

([Call Date] >= [_THISROW].[Period Begin),

([Call Date] <= [_THISROW].[Period End),

(OR(

([Call Type] = “INTERNATIONAL CALL”),

([Call Type] = “INTERNATIONAL MOBILE CALL”)

)

) )

But it doesn’t work +Steve Coile

[Steven_Coile]

The expression you posted contained several typos.

SUM(

SELECT(

CALLS[SECONDS],

AND(

([Customer] = [_THISROW].[Customer]),

([Call Date] >= [_THISROW].[Period Begin]),

([Call Date] <= [_THISROW].[Period End]),

IN(

[Call Type],

LIST(

“INTERNATIONAL CALL”,

“INTERNATIONAL MOBILE CALL”

)

)

)

) )