Countifs with exact match criteria

[cnuklhead]

I am running a complex COUNTIFS formula, to return the total counts of plans, in another table, which meet a certain set of criteria. The COUNTIFS formula is built using INDIRECT as well as specific cell references. I have evolved this formula from a SUMPRODUCT, so I know it works. With the COUNTIFS version, however, sum of my plans end in "*", so that category always returns the total number of plans, instead of just match this specific one. For example...

List of plans to total:
Lightning *
Lightning A
Lightning B
Lightning C

Original Table totals:
Lightning * = 4
Lightning A = 3
Lightning B = 7
Lightning C = 6

My TOTALS table will show:
Lightning * = 20 <== ERROR!!!
Lightning A = 3
Lightning B = 7
Lightning C = 6

For the "Lightning *" category, it is treating the "*" like a wildcard, and I need it to only return the "4" that is the actual count I cannot type the actual plan name ("Lightning *") into the formulas, as I have a large list of plan names, so I am using a cell reference, to reference the name itself. This way, the formula is consistent and can be pasted down through the whole list of plans.

Any suggestions? I am using Excel 2010

Thanks Jill

 

[jasonb75]

Use "Lightning ~*" as your criteria, the tilde prefix tells excel to look for the * character instead of treating it as a wildcard.

 

[Jonmo1]

Welcome to the board....

Sumproduct and Countifs are very different functions..
So just because it works with one function, doesn't absolutely mean it will work with the other.

The big difference in this situation is that
COUNTIF(s) supports the use of wildcards, while Sumproduct does not.
So the Sumproduct was literally looking for *, but countifs was using the * as a wildcard.

So in your cell reference within the countifs formula, precede the * with a tilde ~
e.g. have the countif's search for Lightning ~*

That tells it to treat the * literally.


Hope that helps.

 

[cnuklhead]

Thank you for your quick responses. I am not, however, using the actual plan name as my lookup value. I am using a cell reference. For example:

$B131

The cell B131 has the plan name "Lightning *" in it. So, my formula will be pasted down throughout its column (in this case, Column F), to reference the corresponding plan name in the B Column, of the same row.

Thank you
Jill

 

[Jonmo1]

So in B131 put "Lightning ~*"

 

[cnuklhead]

Making the reference cell be: Lightning ~* returns a result of 0. There should be 1 of them. With no modifications to the COUNTIFS formula, the result is 96 (sum of all plans beginning with "Lightning "

Is there a formatting piece that I could be overlooking?

 

[Jonmo1]

Works for me..

Perhaps it's time to post your ACTUAL formula, and some real sample data..
See my signature below for the Excel Jeanie
That will help you post some meaningfull examples..

Excel Workbook
	A	B	C	D
1	Data		Wrong	Correct
2	Lightning *		Lightning *	Lightning ~*
3	Lightning A		4	1
4	Lightning B
5	Lightning C
Sheet1

 

[cnuklhead]

After I put my brain back in my head, I realized that there was a space where there ought not have been one. It works like a charm now!! thank you so much for your help!!!

On a somewhat related not, I used to use SUMPRODUCT before having 2010 installed, and discovering the COUNTIFS. With the SUMPRODUCT, it takes about 10 minutes to update this workbook. Do you know, all other formula components remaining the same, which of these formula types is most optimal to use?

Thanks again....Jill

 

[Jonmo1]

Glad to help, thanks for the feedback..

All other things being equal, Countifs will be more efficient than Sumproduct.

Because Countifs was designed for the purpose of counting multiple criteria.
Sumproduct, well that's not what it was designed for.
But out of necessity (prior to XL2007), some clever people discovered ways to use it for that purpose.

 

[cnuklhead]

Thank you so much for your help!! I think this will help cut down on a lot of unnecessary finger twiddling time, waiting for formulas to process. My employer thanks you for saving him wages!!!

Jill