COUNTIF or Vlookup

[afountas]

Formula I need help creating.

count the number of times a certain text criteria appears in a list , and then subtract the sum of the column next to it. *** BUT the total cannot be negative, or less than 0.

Thanks,


I'm pretty sure its a COUNTIF formula.

 

[njimack]

=MAX(0,COUNTIF(Range,Criteria)-SUM(Column:Column))

 

[texasalynn]

please post a small sample of what you data looks like and the expected results. It the formula does result in value less than zero what would you want?

 

[T. Valko]

Formula I need help creating.

count the number of times a certain text criteria appears in a list , and then subtract the sum of the column next to it. *** BUT the total cannot be negative, or less than 0.

Thanks,


I'm pretty sure its a COUNTIF formula.

It's not real clear what you want to do. If you can post a few rows of sample data and tell us what result you expect it will help us understand.

 

[Desu Nota from Columbus]

=IF((SUMPRODUCT(--(F1:F29="TEXT CRITERIA"))-COLUMNS(A:A:F:F)-1)>0),SUMPRODUCT(--(F1:F29="TEXT CRITERIA"))-COLUMNS(A:A:F:F)-1,"")



Assuming your list to be searched is in Column F (Row 1 to 29).

 

[afountas]

I have a list of malls like 3,000 malls and need to see how many "Starbucks" stores are in the mall, and within 5 Miles. Column B represents in mall (and will only be a value of 1 or 0) . And, C is 5 mile radius. I have a list of the stores within 5 miles of the store, and for example: "Mall 1" has 300 starbucks locations within 5 miles, including the 1 location in mall. So my Column C count needs to be the total -1, but can never be negative


Starbucks

A B (in mall) C (5 mile)

Mall 1 1 299
Mall 2 0 1
Mall 3 1 -1 ----this cannot happen
Mall 4

 

[Desu Nota from Columbus]

Do you want a cumulative total for all the malls (one big number)?

or:

Do you want a total for each mall?
---For this just put D1: =IF(B1=1,C1-1,C1) and drag this down column D for each mall (~3,000 rows (double click the black box in bottom right when cell D1 is selected))

 

[afountas]

Hopefully I can explain more concisely.

What the formula needs to do:

1. Count the # of occurances a specific string of text in a column
2. Subtract the value of another column
3. The Value must be greater or equal to 0

ie.

if

if the text "Great Mall' occurs 1 time

Column A= 1 Column B = 0 ---- column b cannot be negative


If Great Mall apprears 2 times

Great Mall
Great Mall

Column A = 1 Column B=1

If Great Mall Appears three times

Great Mall
Great Mall
Great Mall

Column A = 1 Column B=2


if the text "Great Mall' NEVER OCCURS


Column A= 0 Column B = 0

 

[Desu Nota from Columbus]

<table style="width: 572px; height: 437px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2450;width:50pt" width="67"> <col style="mso-width-source:userset;mso-width-alt:1828;width:38pt" width="50"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:1353;width:28pt" width="37"> <col style="mso-width-source:userset;mso-width-alt:2450;width:50pt" width="67"> <col style="mso-width-source:userset;mso-width-alt:3401;width:70pt" width="93"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:50pt" height="17" width="67">MALL</td> <td class="xl24" style="width:38pt" width="50"> In Mall</td> <td class="xl24" colspan="2" style="mso-ignore:colspan;width:76pt" width="101"> Near Mall</td> <td class="xl24" style="width:50pt" width="67">
</td> <td class="xl24" style="width:70pt" width="93">
</td> <td class="xl24" style="width:48pt" width="64">
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Great Mall</td> <td align="right">1</td> <td align="right">100</td> <td>
</td> <td class="xl24">Mall</td> <td class="xl24"> # Occurences</td> <td class="xl24"> # SB</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Fish Mall</td> <td align="right">1</td> <td align="right">100</td> <td>
</td> <td>Great Mall</td> <td align="center">4</td> <td align="center">398</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Cap Mall</td> <td align="right">0</td> <td align="right">100</td> <td>
</td> <td>Cap Mall</td> <td align="center">3</td> <td align="center">298</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Great Mall</td> <td align="right">0</td> <td align="right">100</td> <td>
</td> <td>Fish Mall</td> <td align="center">4</td> <td align="center">398</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Fish Mall</td> <td align="right">1</td> <td align="right">100</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Cap Mall</td> <td align="right">1</td> <td align="right">100</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Great Mall</td> <td align="right">0</td> <td align="right">100</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Fish Mall</td> <td align="right">0</td> <td align="right">100</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Cap Mall</td> <td align="right">1</td> <td align="right">100</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Great Mall</td> <td align="right">1</td> <td align="right">100</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Fish Mall</td> <td align="right">0</td> <td align="right">100</td> <td>
</td> <td>
</td> <td>
</td> <td>



</td> </tr> </tbody></table>

 

[Desu Nota from Columbus]

Is what I posted what you want?

You have your raw data in columns A-C (Mall Name, # In Mall, # in 5 Miles)


To the right of the raw data is a list of the malls with the previously discussed math (total starbucks in area minus 1 or 0---depending on value of Column B)


If this still doesn't meet your needs, please use HTML Maker or ExcelJeanie to post a sample of your data and what you want it to look like afterward (using sample data).