ADDING an INDIRECT statement to only count a specific criteria

[jmurray394]

Hi! I have a workbook that has a master tab and 2 data tabs. The master tab lists all the companies that owe $. I have two working formulas that work. One that counts the number of occurrences that company shows up in the subsequent tabs, and a second formula that sums the total $ of that company across the tabs.

1. =SUMPRODUCT(COUNTIF(INDIRECT(“’”&ORGS&”’!A2:A700”),A2))

2. =SUMPRODUCT(SUMIF(INDIRECT(“‘“&ORGS&”’!A2:A700”),A2,INDIRECT(“’”&ORGS&”’!H2:H700”)))



I want to add one more criteria to both of these formulas

1. To only count the number of occurrences that company shows up in the subsequent tabs only if its completed status is “No”.

2. To only sum the total $ of that specific company across all the tabs only if the completed stats is “No”

 

[Rijnsent]

Hi j,
how do you know if the completed status is "No", is that in another column? If so, you could try e.g. COUNTIFS & SUMIFS.
Cheers,
Koen

 

[jmurray394]

Hi j,
how do you know if the completed status is "No", is that in another column? If so, you could try e.g. COUNTIFS & SUMIFS.
Cheers,
Koen

The last column (column R) in the row has the completed status. I also already have the original COUNTIF and SUMIF in

 

[Rijnsent]

Untested, something like this:
SUMIFS(INDIRECT(“’”&ORGS&”’!H2:H700”),INDIRECT(“‘“&ORGS&”’!A2:A700”),A2,INDIRECT(“‘“&ORGS&”’!R2:R700”),"No")