Return row count that meet multiple criteria

(0) Share

Report

Posted on by Armyrunner

I have a SharePoint list with three columns

Column 1 = Fruit (Single line text)

Column 2 = Ripe (Single line text)

Column 3 = Destroyed (Date)

I am looking to display the count of how many rows meet a set of criteria within column 1 and 2 and where column 3 is blank using a label on my Power App screen.

Example of desired outcome:

I am looking for the row count with Fruit=Apple, Ripe=Yes, Destroyed=BLANK

This result would display 2 from the list sample below.

Fruit Ripe Destroyed

Apple Yes 7/5/20

Orange Yes 6/5/20

Pear Yes 5/2/20

Apple No 4/6/21

Apple Yes

I can get one criteria to work with this formula CountRows(Filter('DATA',Fruit="Apple")) but when I try adding more criteria into the formula it fails CountRows(Filter('DATA',Fruit="Apple" && Ripe="Yes" && Destroyed=""))

I would appreciate the help. Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

WarrenBelz 153,408 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @Armyrunner ,

Assuming Ripe is a Text field (not yes/no), this should work

CountRows( Filter( 'DATA', Fruit="Apple" && Ripe="Yes" && Destroyed = Blank() ) )

If it is a yes/no field

With( { wData: Filter( 'DATA', Fruit="Apple" && Destroyed = Blank() ) }, CountRows( Filter( wData, !Ripe ) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Armyrunner 28 on at

Like (0)

Report
Copy link

Link copied!

Thank you and I gave it a try. My list has 334 rows with this criteria and the formula is displaying 47. For some reason it is not capturing all the rows. Ideas?

Was this reply helpful? Yes No
WarrenBelz 153,408 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@Armyrunner ,

Are you saying there is 334 rows with the criteria? Please confirm SharePoint is your data source and whether Ripe is a yes/no or Text field. Also how many items are in the total list and please look at my amended post.

Was this reply helpful? Yes No
Armyrunner 28 on at

Like (0)

Report
Copy link

Link copied!

The SharePoint list has 4126 rows/records in total. When I manually sort the list with the criteria, I come up with 334 records. The data in the list was imported from an Excel spreadsheet. The Fruit and Ripe column is confirmed as a single line of text. The Destroyed column is confirmed as a Date and Time column with Date only selected.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,408 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@Armyrunner ,

Yes - the issue was what I suspected (Delegation - you might have a read of this blog of mine) - a Date was not what I was expecting from you post - try this

With( { wData: Filter( 'DATA', Fruit="Apple" && Ripe = "Yes" ) }, CountRows( Filter( wData, Value(Destroyed) = 0 ) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Armyrunner 28 on at

Like (0)

Report
Copy link

Link copied!

Thank you ... that appears to calculate and return the correct number.

Was this reply helpful? Yes No