Reducing & Counting Values Across Multiple Columns

[JCambareri]

Hi everyone, hoping someone can help me out here!

I'm trying to convert some data into information that is more usable for my purposes. My management system populates an Excel Spreadsheet with large lists of submissions. The relevant data in the spreadsheets are;
Column A: Name - there are about 177 names on each list occurring multiple times Column B: Number - all numbers start with the same 2 letters, but have 0 random numbers that follow immediately after. This field is also sometimes left empty.
Column C: Status - there are multiple status', I need to take all the ones that start with the word "declined" and count them.

What I'm trying to do is group all of the occurrences of the same name in column A then count how many times a number appears in a column next to that person's name. Then count how many times status' that being with the word "Decline" occur next to that person's name.

I'm not sure I'm explaining this very well though, so hopefully the upload will help. Any assistance would be greatly appreciated!

 

[Sajan]

Hello Jcambareri,
I am not sure I am following your description above.

Can you add a few rows of manually worked out examples for the data you supplied? That might shed some light on what you want to do.

Cheers,
Sajan.

 

[JCambareri]

Sure, I've attached a copy of what I'm trying to accomplish. See sheet 2 of the example.


Column A is the name.
Column B is how many times the name occurs on Sheet 1.
Column C is used to sum additional columns added between B & C in the future.
Column D shows how many times a number appears next to Person A's name on Sheet 1, column B
Column E shows how many times a cell beginning with the word "Decline" appears next to person A's name.

Hope that helps.

 

[JCambareri]

Also, if there is a way to plug in a formula into Sheet 1, column B that says:
"If blank, return "0"; If any other value return "1""

& in column C a formula that does:
"If first letter in cell is "D", return "1", if first letter in cell is anything else, return "0""

Then I should be able to solve the rest of the problem with a pivot table.

 

[SirJB7]

Hi, JCambareri!

Give a look at the uploaded file. Formulas for worksheet Sheet 2 are:
B2: =CONTAR.SI(Sheet1!A:A;A2) -----> in english: =COUNTIF(Sheet1!A:A,A2)
C2: reserved for future use
D2: =SUMA(B2:C2) -----> in english: =SUM(B2:C2)
E2: =CONTAR.SI.CONJUNTO(Sheet1!A:A;A2;Sheet1!B:B;"> ") -----> in english: =COUNTIFS(Sheet1!A:A,A2;Sheet1!B:B,"> ")
F2: =SUMAPRODUCTO((Sheet1!A:A=A2)*(IZQUIERDA(Sheet1!C:C;8)="Declined")) -----> in english: =SUMPRODUCT((Sheet1!A:A=A2)*(LEFT(Sheet1!C:C,8)="Declined"))

For better performance, at least in column F formula, adjust the whole column references to the exact ranges, or better indeed define dynamic named ranges as follows:
=OFFSET($A$2,,,COUNTA($A:$A)-1,1)

Just advise if any issue.

Regards!

 

[Sajan]

On Sheet2, to get the counts in col B, put in cell B2 and copy down
=COUNTIF(Sheet1!$A$2:$A$19,Sheet2!$A2)

On Sheet2, column D, put in cell D2 and copy down:
=SUM(COUNTIF($A2,IF((Sheet1!$A$2:$A$19=$A2)*(Sheet1!$B$2:$B$19<>""),Sheet1!$A$2:$A$19)))
enter with Ctrl + Shift + Enter

On Sheet2, column E, put in cell E2 and copy down:
=SUM(COUNTIF(A2,IF((Sheet1!$A$2:$A$19=$A2)*(LEFT(Sheet1!$C$2:$C$19,7)="Decline"),Sheet1!$A$2:$A$19)))
enter with Ctrl + Shift + Enter

Can you clarify your most recent post? You have some values in ColumnB on sheet 1. Do you want that replaced with 1s and 0s?

Cheers,
Sajan.

 

[Sajan]

Hi SirJB7,
Did not see your post in time!

-Sajan.

 

[SirJB7]

@Sajan
Hi!
Welcome to the club.
Regards!

 

[JCambareri]

Thank you both so much! The formulas are working very well.

I'm just running into one more related problem now.
Every month I have to compile this list every month and keep the monthly data separate. So what I've done is created a separate sheet that totals the "binds" and "declines" for each name side by side. I'm trying to get the sum of those numbers onto a 3rd sheet.

I tried to just bastardize the formulas above to come up with this, but it's not working properly:
=SUM(COUNTIF($A$4,IF((PivotSheet!$B$4:$B$190),(PivotSheet!$E$4:$E$190),(PivotSheet!$H$4:$H$190)=PivotSheet!$A$4)))

I've attached another example file. In that example I want to take the sum of each person's column B, E & H and insert them into Sheet2, column B.

 

[SirJB7]

Hi, JCambareri!

Try this in worksheet Sheet 2:
B2: =SUMAR.SI(Sheet1!$A:$A;$A2;Sheet1!B:B)+SUMAR.SI(Sheet1!$A:$A;$A2;Sheet1!E:E)+SUMAR.SI(Sheet1!$A:$A;$A2;Sheet1!H:H) -----> in english: =SUMIF(Sheet1!$A:$A,$A2,Sheet1!B:B)+SUMIF(Sheet1!$A:$A,$A2,Sheet1!E:E)+SUMIF(Sheet1!$A:$A,$A2,Sheet1!H:H)

Then copy across and down as required.

Regards!

 

[SirJB7]

Hi, JCambareri!

If you happen to have to manually build the unique person list frequently, you could use this technique:
http://chandoo.org/forum/threads/extract-records-between-two-dates-excel-2010.7084/#post-41179

In my uploaded file you could use this in column A of worksheet Sheet2:
A2: =SI.ERROR(INDICE(Sheet1!A$2:A$19;COINCIDIR(0;INDICE(CONTAR.SI(A$1:A1;Sheet1!A$2:A$19);0;0);0));"") -----> in english: =IFERROR(INDEX(Sheet1!A$2:A$19,MATCH(0,INDEX(COUNTIF(A$1:A1,Sheet1!A$2:A$19),0,0),0)),"")

With this technique using whole columns like A:A isn't recommended, so you'd better define a dynamic named range so as to get rid of references adjusts:
RangeList: =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1,1)
and in the previous formula replace this:
Sheet1!A$2:A$19
by this:
RangeList
being the formula:
=IFERROR(INDEX(RangeList,MATCH(0,INDEX(COUNTIF(A$1:A1,RangeList),0,0),0)),"")

Regards!

 

[JCambareri]

Thanks again! Can't tell you how much I appreciate it guys!

 

[SirJB7]

Hi, JCambareri!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!