Alternate for SUM(COUNTIFS(..))

asked 2018-02-17 03:25:41 +0100

Sanjay Prasad gravatar image

I have large excel file with list of new connections of my company with Dates in Column T, Place Names in Column AA & Status of connection (New Customer / Old Customer) in Column D. I have used COUNTIFS so far to get count of New Customers, Old Customers individually based on one date & one place.

=COUNTIFS($Data.T1:T95536, D1, $Data.AA1:AA95536, B6, $Data.D1:D95536, "New") Where, D1= Date I enter manually B6 to B14 are Place Names (fixed format by the Company) filled with same formula

Formula for "Old" is written in another column.

Now, I want to have count of both New & Old customers in a column (Column R in this case) in another Sheet like below.

=SUM(COUNTIFS($Data.T1:T95536, R71, $Data.AA1:AA95536, C60, $Data.D1:D95536, {"New","Old"})) R71, S71, T71..... = Dates in increasing order manually entered as reference. C60, C61.... C68 = Name of places (Fixed format)

This formula is working in Microsoft Office but not working in Libre Office.

Is there any alternative formula to fulfill my purpose. Please guide.

edit retag flag offensive close merge delete

Comments

Maybe you will try to use the PivotTable to solve this problem?

JohnSUN gravatar imageJohnSUN ( 2018-02-17 08:24:02 +0100 )edit

Do you use Ctrl+Shift+Enter, to introduce the SUM() formula as array?

m.a.riosv gravatar imagem.a.riosv ( 2018-02-17 21:47:14 +0100 )edit

This formula is working in Microsoft Office but not working in Libre Office.

What is not working? Any error message? Which LibreOffice (without a space!) version are you using?

Dennis Roczek gravatar imageDennis Roczek ( 2018-02-18 03:51:36 +0100 )edit