SumCountIF

[Sunline]

hello all , i have this function which does numbers ,
=Sum(Countif(DG2:DI2,{"1",,"1="}))

Iwould like to get it to do currency
<$10,000.00
>$9,000.00<$21,001.00

And with decimal point
<1.9
>2.3<4.6

Just wanting basic funtions so i can change the above examples as needed .
Thanks .

 

[Sunline]

Sorry should only be one comma after "1"

=Sum(Countif(DG2:DI2,{"1","1="}))

Thanks .

 

[mikerickson]

What does the formula do?

 

[Sunline]

Hi , it counts the number of times 1 or 1= appears in range DG2:DI2 .
Thanks .

 

[Sunline]

Bump , i think is the phrase , thanks .

 

[markmzz]

hello all , i have this function which does numbers ,
=Sum(Countif(DG2:DI2,{"1",,"1="}))

Iwould like to get it to do currency
<$10,000.00
>$9,000.00<$21,001.00

And with decimal point
<1.9
>2.3<4.6

Just wanting basic funtions so i can change the above examples as needed .
Thanks .

Try this array formulas (use Ctrl+Shift+Enter and not only Enter):


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">R$ 20.000,00 </TD><TD style="TEXT-ALIGN: right">1,1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">1=</TD><TD style="TEXT-ALIGN: right">R$ 9.000,00 </TD><TD style="TEXT-ALIGN: right">3,2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A3</TH><TD style="TEXT-ALIGN: left">{=SUM((A1:A2="1")+(A1:A2="1="))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B3</TH><TD style="TEXT-ALIGN: left">{=SUM((B1:B2<10000)+(B1:B2<21001)*(B1:B2>9000))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C3</TH><TD style="TEXT-ALIGN: left">{=SUM((C1:C2<1.9)+(C1:C2>2.3)*(C1:C2<4.6))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

</TD></TR></TBODY></TABLE>


Markmzz

 

[Matty]

I'm struggling to understand what you're wanting to do (as are others, judging from the limited response).

Could you clarify further? A small example, along with the expected result, is usually the best way to illustrate things.

Matty

 

[Sunline]

Ok , thanks Markmzz will try these tonight .
Matty i apoligise for not asking and giving the right info , im learning alot from here and appreciate everyone that helps me , after a while i will look to give back if i can answer someones question , thanks both for your feedback .