Count If based on 2 condition

[ali_elshafee111]

hello,

I have two sheets, the 1st one is the master sheet for training data and the 2nd one is the list of training requests, I want to count all those request form employee related to each training name was selected and the request status is approved

[Marc_Dillon · Accepted solution]

https://help.appsheet.com/en/articles/2347641-count

https://help.appsheet.com/en/articles/2357314-select

https://help.appsheet.com/en/articles/2347624-and

https://help.appsheet.com/en/articles/4575708-list-dereference

[ali_elshafee111]

Thanks for your reply

I try many combination for days but this are not working even if found some solution here but also not working  I don't Know why as am new for AppSheet 

[Marc_Dillon]

You want the number of approved TrainingRequest records per each Training record? You want this added as a new Virtual Column in the Training table?

The [Related Training Request] VC is already a list of Requests per Training. So you actually only have a single condition on top of that, so no need for AND()

You can use 'List Dereference' off of this [Related...] VC. It doesn't really matter which column you deref into, since you're just getting a count, it will be the same no matter what.

Wrap a SELECT() around that List Deference to filter it down for the [status]=approved condition.

Then wrap the whole thing in COUNT.

[ali_elshafee111]

sorry marc as I said am new in AppSheet so I didn't understand you 

sorry again if you can explain more

also the the [Related Training Request] column is reading from a slice for the training request while status is approved and I don't know why when count this row give me all the requests not the approved one only

[Marc_Dillon]

Which part is confusing you?

Since that's the first (only?) REF_ROWS expression on the Training table, I'm assuming you've modified the auto-generated one. I would suggest against that, you should put it back to how it was originally, then create an additional VC to reference the Slice. Also if that Slice is already only the approved records, you can simply use COUNT() on its [Related...] VC.

[ali_elshafee111]

first  I would like to thanks for the support

ok this is the results of working with the slice from training request table while the status is approved 

the approved request should be 4 not 6 (6 is all the requests)

and this is when i used the original table "Training request"  it bring all request as following

[Marc_Dillon]

Your REF_ROWS VC is probably still pointed at the base table, instead of the Slice. You have to change both the expression and the table selection that's a bit lower down in the column config.

[ali_elshafee111]

I would like to thank you I found my mistake and this was work with me 

count(select(Training Request[Training Name],and([_THISROW]=[Training Name],[Status]="Approved")))

[Tommy62]

Versuche:

COUNT(SELECT ([Related Training Requests], 
  [Status] = "genehmigt"))

[Tommy62]

Versuche: 

COUNT(filter ("Training Requests", 
  AND(
   [Training Name] <= [_THISROW].[Training Name],
   [Status] = "genehmigt")))

[ali_elshafee111]

Thanks for your reply

I try it also but it doesn't work after a little modification , this always give me a zero results while it should read a number 

COUNT(filter ("Training Request",
AND(
[Training Name] = [_THISROW].[Training Name],
[Status] ="Approved")))