How can I pull data from a table conditionally ?

General discussion on OpenOffice Calc and its spreadsheets.

How can I pull data from a table conditionally ?

Postby Jalium » Sat Nov 01, 2014 6:25 am

Hi,

How could I write a statement that searches a given field of cells and adds up numbers only if the adjacent cells match certain requirements ?

For ex. If I wanted the sum of all units for the provider "JL" that had the CPTcode that starts with "GP:9"

********************************************
Provider / CPTcode / Units
JL GP:97112 3
JL 1101F 1
KA GP:97001 1
JL GP:97110 1
MM G8539 1
********************************************

I've tried pivot tables, but then I still have to manually pick out the codes I want to add.
Thank you for any suggestions!!!
Jalium
 
Posts: 20
Joined: Tue Mar 25, 2014 6:26 am

Re: How can I pull data from a table conditionally ?

Postby Joseph S. Klein » Sat Nov 01, 2014 7:39 am

Hello Jalium,

The SUMPRODUCT() function provides a way to do this. If your data are in A2:C6
Code: Select all
=SUMPRODUCT(A2:A6="JL";LEFT(B2:B6;4)="GP:9";C2:C6)
Joseph S. Klein
 
Posts: 38
Joined: Mon Mar 24, 2014 6:28 am

Re: How can I pull data from a table conditionally ?

Postby Jalium » Sat Nov 01, 2014 9:12 am

Thank you so much for the response. That seems to be exactly what I was looking for.

Q: How could I add another string requirement for column B ?
(ex. I want any CPTcodes that start with "GP:9" OR just "9".)
Jalium
 
Posts: 20
Joined: Tue Mar 25, 2014 6:26 am

Re: How can I pull data from a table conditionally ?

Postby Joseph S. Klein » Sat Nov 01, 2014 11:07 am

For two conditions related by "or", I would use two separate SUMPRODUCT() and add the results. Expressing complicated combinations of conditions will quickly get out of hand.
Joseph S. Klein
 
Posts: 38
Joined: Mon Mar 24, 2014 6:28 am


Return to OpenOffice.org Calc

Who is online

Users browsing this forum: No registered users and 0 guests

cron