want to count number of items in 2 seperate columns if both are more than zero

[excel_help_wanted]

Hello,

I am trying to figure out number and dollar sum of new items that were introduced. I have a huge data dump of 60,000 rows. Column A has current year dollar sales and column B has Dollas Sales Change vs Year Ago.

An item is new if Column A has dollar sales and Column B ($ sales chg vs Year Ago) also has the same dollar sales number.

Since it's a huge data dump, I only want to get a count of items that have the same dollar sales value in Column A and Column B and sum of dollar sales for the same.

Thanks

 

[HalfAce]

Hi and welcome to the board.
Here are a couple ideas for you.
One (a formula approach)
In another column somewhere (I used column E for this example) in E1 enter the formula:

=IF(A1=B1,A1,"")

and copy down column E as far as your data goes in columns A & B.Then, where ever you want your total, enter a simple sum formula:

=SUM(E1:E60000)

- Or, if in a column other than E: =SUM(E:E)


Or, for a vba approach:

Sub Demo()

Dim LstRw As Long, Rw As Range, Total As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
Total = 0
For Each Rw In Range("A2:A" & LstRw)
  If Rw.Value = Rw(, 2).Value Then Total = Total + Rw.Value
Next Rw

MsgBox Total

End Sub

Either one of these help?

 

[rdw72777]

Probably some version of array formulas. Try the following:

=COUNT(IF($A$2:$A$60000=$B$2:$B$60000,$B$2:$B$60000))

=SUM(IF($A$2:$A$60000=$B$2:$B$60000,$B$2:$B$60000,0))

You'll obviously need to update the data ranges to match your file, but it should work.

BTW, with array formulas, you need to hit CTRL+SHIFT+ENTER to get them to work correctly, hitting ENTER only causes errors.

 

[excel_help_wanted]

Probably some version of array formulas. Try the following:

=COUNT(IF($A$2:$A$60000=$B$2:$B$60000,$B$2:$B$60000))

=SUM(IF($A$2:$A$60000=$B$2:$B$60000,$B$2:$B$60000,0))

You'll obviously need to update the data ranges to match your file, but it should work.

BTW, with array formulas, you need to hit CTRL+SHIFT+ENTER to get them to work correctly, hitting ENTER only causes errors.

Thanks for the help. Both the formulas worked!

 

[excel_help_wanted]

Thanks for the help....they worked!!!

Hi and welcome to the board.
Here are a couple ideas for you.
One (a formula approach)
In another column somewhere (I used column E for this example) in E1 enter the formula:

=IF(A1=B1,A1,"")

and copy down column E as far as your data goes in columns A & B.Then, where ever you want your total, enter a simple sum formula:

=SUM(E1:E60000)

- Or, if in a column other than E: =SUM(E:E)


Or, for a vba approach:

Sub Demo()
 
Dim LstRw As Long, Rw As Range, Total As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
Total = 0
For Each Rw In Range("A2:A" & LstRw)
  If Rw.Value = Rw(, 2).Value Then Total = Total + Rw.Value
Next Rw
 
MsgBox Total
 
End Sub

Either one of these help?