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
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โ)
)
) )
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
+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?
@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
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
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โ
)
)
)
) )
User | Count |
---|---|
39 | |
35 | |
29 | |
23 | |
18 |