Countif referance two cells

[blue-bullet]

Hi again, now i have this formula:

=COUNTIF(INDIRECT(A2&"_RES"),"Yes")
A2 refers to a cell to complete the indirect query (DCA_RES) which refers to another range in the workbook.

Now I want to have count if A2&"_INIT" is "BB" AND if A2&"RES" is "YES"

So that I get the amount of records of resolved queries (Yes) that match my Initials (BB).

I cant think of a different formula that might work?

[DonkeyOte]

If we assume A2 is DCA... are you saying the calculation is multi conditional, ie

(DCA_INIT = "BB") AND (DCA_RES = "YES")

ie the two calcs are not independent of one another ?

If so then pre-XL2007 you would need to revert to a SUMPRODUCT given your existing setup, ie:

=SUMPRODUCT((INDIRECT(A2&"_INIT")="BB")*(INDIRECT(A2&"_RES")="YES"))

However SUMPRODUCT is expensive and the above, given use of INDIRECT, is Volatile so if you have lots of these calcs and/or the ranges are big then performance is likely to take a noticeable hit.

If that's the case then I woudl suggest that if the ranges are on the same sheet etc then you would be best served creating a concatenation column (and subsequent named range) and using that within a far more efficient (yet still volatile) COUNTIF.

[blue-bullet]

Thanks Donkey, I am getting the #NUM! error with that one?

[DonkeyOte]

I suspect your defined names are referring to entire columns... pre XL2007 you can't use Arrays/Sumproduct with entire column references and nor should you want to in truth as performance would be horrendous.

Can you outline the RefersTo elements of your Names ?

[blue-bullet]

I suspect your defined names are referring to entire columns... pre XL2007 you can't use Arrays/Sumproduct with entire column references and nor should you want to in truth as performance would be horrendous.

Can you outline the RefersTo elements of your Names ?

The definitions relate to entire columns but where not talking megs of data and it is only to count instances effectively. So only count rows that include BB and Yes at the same time (else all rows with yes in will be added to rows that have BB in)

[DonkeyOte]

OK. but I think you're missing my point(s).

Either

a) if you wish to persist with multi conditional testing (ie SUMPRODUCT) you must revise your Named Ranges
the physical quantity of data resident within the range is irrelevant per se - rather it is the dimensions of the range itself that is important in terms of avoiding the #NUM! error
You may wish to review the link to Dynamic Named Ranges in my sig.

b) (advised) create a concatenation column the concatentes the column that may include BB and the column that may include Yes.
Let's say for sake of demo they are columns A & B respectively.... at which point

C2: =A2&":"&B2
copied down

Utilising the above means you can revert to COUNTIF as before, eg:

=COUNTIF(C:C,"BB:Yes")

The above is much more efficient than SUMPRODUCT and as illustrated use of entire columns is not an issue.

[blue-bullet]

Ok option B sounds good. It is a bit unclear to what I set to what though, please expand?

[DonkeyOte]

If you give me some more info - or specific details I can give you an example... all we know presently is that you have 2 named ranges - one of which may contain BB and the other may contain Yes... we don't know where either are located nor do we know where you would want to place the concatenation column.

[blue-bullet]

If you give me some more info - or specific details I can give you an example... all we know presently is that you have 2 named ranges - one of which may contain BB and the other may contain Yes... we don't know where either are located nor do we know where you would want to place the concatenation column.

Ok so,

Sheet 1 Inlcudes a list of devices to summaries what and how many devices have issues, during a build this is. Summaries come down to, how many? Who assigned to(Initials)? and If it is resolved.(Res)

column F will include referance to what ever is in column A add it will "_BB" giving you DCA_BB for example

Sheet 2(+) includes a list of issues, A:Issue number, B:Resolved?, C:Init(Initials), D:Issue Descr.

B:B is a range as Sheet2_RES
C:C is a range as Sheet2_Init

Hope that is clear if not let me know, thanks for you help btw

[DonkeyOte]

If I've understood...

Sheet2(+):
E1: =B1&":"&C1
copied down for all rows
(obviously group all sheets if you wish to apply above to all simultaneously)

You can if you want to create a named range for each sheet but I wouldn't bother myself given the location is identical for each sheet and so in general your formula on the summary sheet would simply become

=COUNTIF(INDIRECT("'"&$A$2&"'!E:E"),"Yes:BB")

obviously BB and Yes can be cell references, ie

=COUNTIF(INDIRECT("'"&$A$2&"'!E:E"),B$2&":"&$A3)
where B2 contains Yes/No and A3 holds initials (BB etc..)

EDIT: I believe you've revised/added some additional references since the above was composed but you should be able to get the gist from the above (?)

[blue-bullet]

Thanks, I will give a poke at that one and let you know, thanks for now.