Countif formula

[JMEEE3]

HELLO I WOULD BE GRATEFUL IF ANYBODY COULD HELP ME WITH A COUNTIF FORMULA WHERE I AM TRYING TO FIGURE OUT HOW MANY NEW SALES AND REWRITES AN AGENT IS DOING PER MONTH SO WE DISTINGUISH THE AGENTS BY A #1,2,3 THE SALES FOR AGENT 1 ARE ON COLUMN I7:I77 AND IF ANYWHERE IN THAT CELL THEY PUT A "1" THAT WAS THEIR SALE BUT THAN IN F7:F77 ITLL SHOW IF IT IS "NEW" OR "REW" HOW CAN I WRITE THE FORMULA WHERE IT WILL COUNT ALL OF THE "1s" IN I7:I77 ONLY IF THERE IS A "NEW" IN F7:F77. AND SO ON WITH THE OTHER AGENTS I WISH I COULD UPLOAD A PICTURE FOR AN EXAMPLE BUT I HAVE YET TO FIGURE THAT OUT. I FIGURED IT WOULD BE A LONG FORMULA BUT WHAT I HAVE SO FAR IS SOMETHING LIKE =COUNTIF(I7:I77,"1",F7:F77,"NEW") ???

 

[Scott Huish]

It's very hard to read when you type in all caps, but here you go:

=SUM(COUNTIFS(I7:I77,1,F7:F77,{"NEW","REW"}))

 

[jtakw]

Hi,

I think OP's keyboard is stuck in "Caps Lock", and some of the punctuation keys are probably missing.

 

[JMEEE3]

Sorry about that, I input the formula and it gives me a result of "0". SO this is how my table looks like a bit the type of sale and than the 1,2,3 represents the agents "1" is I7:I77 down the column they have put a "1" that's their sale and to the side I'm trying to count how many of those sales are "new" . I am wondering why I keep getting a "0" meaning it didn't count anything.
Type* Referral type* A/F 1 2 3 4 5
REW REW * $ * * *49.87* 1
NEW WALK-IN *$ * * *123.00* 1 New Sales*
REW REW * $ * * *35.00* 1 1 2 3 4 5
REW REW * $ * * *35.00* 1 8 0
REW REW * $ * * * * - ** 1
NEW* NEW * $ * * *66.98* 1
NEW* WALK-IN *$ * * *135.00* 1
NEW* WALK IN* *$ * * *210.49* 1 Re-Write*
NEW* WALK IN* *$ * * 55.00* 1 1 2 3 4 5
REW REWRITE *$ * * *67.01* 1 12
REW REWRITE *$ * * *102.43* 1
REW REWRITE *$ * * *39.80* 1
NEW* WALK IN* *$ * * *125.00* 1

 

[Scott Huish]

Is this how your sheet looks? There doesn't appear to be anything in that set that matches the conditions.

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Type*</td><td >Referral</td><td >type*</td><td >A/F</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >REW</td><td >REW</td><td >*</td><td >$</td><td >*</td><td >*</td><td >*49.87*</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >NEW</td><td >WALK-IN</td><td >*$</td><td >*</td><td >*</td><td >*123.00*</td><td style="text-align:right; ">1</td><td >New</td><td >Sales*</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >REW</td><td >REW</td><td >*</td><td >$</td><td >*</td><td >*</td><td >*35.00*</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >REW</td><td >REW</td><td >*</td><td >$</td><td >*</td><td >*</td><td >*35.00*</td><td style="text-align:right; ">1</td><td style="text-align:right; ">8</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >REW</td><td >REW</td><td >*</td><td >$</td><td >*</td><td >*</td><td >*</td><td >*</td><td >-</td><td >**</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >NEW*</td><td >NEW</td><td >*</td><td >$</td><td >*</td><td >*</td><td >*66.98*</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >NEW*</td><td >WALK-IN</td><td >*$</td><td >*</td><td >*</td><td >*135.00*</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >NEW*</td><td >WALK IN</td><td >*$</td><td >*</td><td >*</td><td >*210.49*</td><td style="text-align:right; ">1</td><td >Re-Write*</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >NEW*</td><td >WALK IN</td><td >*$</td><td >*</td><td >*</td><td >55.00*</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >REW</td><td >REWRITE</td><td >*$</td><td >*</td><td >*</td><td >*67.01*</td><td style="text-align:right; ">1</td><td style="text-align:right; ">12</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >REW</td><td >REWRITE</td><td >*$</td><td >*</td><td >*</td><td >*102.43*</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >REW</td><td >REWRITE</td><td >*$</td><td >*</td><td >*</td><td >*39.80*</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >NEW*</td><td >WALK IN</td><td >*$</td><td >*</td><td >*</td><td >*125.00*</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

 

[JMEEE3]

 

[JMEEE3]

Work by jj0377 | Photobucket

 

[JMEEE3]

I have attached a link of a picture of the report, on that report to the right it has a section where the # new sales per agent and below that should be how much those sales are the box below that is for the rewrites how many per agent and how much those are. I hope I am explaining myself in a way you can understand me.

 

[Scott Huish]

Except for adding absolute references, the formula i gave works fine, did you change anything in it?

=SUM(COUNTIFS($I$7:$I$77,1,$F$7:$F$77,{"NEW","REW"}))

 

[JMEEE3]

OKAY YES I DID, I PUT IT WHERE IT JUST HAD "NEW" BECAUSE THATS WHAT I AM LOOKING FOR RIGHT NOW AND THEN THE "REW". would IT BE A DIFFERENT FORMULA IF I WANT TO DO IT INDIVIDUALLY?