Sumproduct In VBA

[Abhijeet]

Hi

I have this formula =SUMPRODUCT((G3<=$C$3:$C$100)*(H3>=$B$3:$B$100)*(F3=$A$3:$A$100))

I want VBA solution for this because data is huge & i want Dynamic Range.
In Excel i use 2 method Table & Define Name but these also not time consume so please tell me VBA solution for this formula
In Attach file i use this formula please help me

 

[chirayu]

Assuming Column A doesn't have blank cells between data

Sub Formulater()

Dim Rng As String

Rng = Cells(Rows.Count, "A").End(xlUp).Row

Range("I3:I" & Rng).Formula = _
"=SUMPRODUCT((G3<=$C$3:$C$" & Rng & ")*(H3>=$B$3:$B$" & Rng & ")*(F3=$A$3:$A$" & Rng & "))"

End Sub

 

[Deepak]

I have changed sumproduct to counifs

 

[Abhijeet]

I have changed sumproduct to counifs

Countifs is not work in my this data told by Narayan Sir

 

[chirayu]

Countifs is not work in my this data told by Narayan Sir

Did you try the macro I gave you?

 

[Abhijeet]

Yes its work. Can u please tell me my data is huge so its work slow or If work slow then any solution for this

 

[Deepak]

Yes its work. Can u please tell me my data is huge so its work slow or If work slow then any solution for this

Countif is faster than sumproduct so it is helpful for large database.

 

[chirayu]

Yes its work. Can u please tell me my data is huge so its work slow or If work slow then any solution for this

The macro calculated by dynamic range. It figures out how many rows you have and accordingly creates the range for the formula. Macro shouldn't be slow.

But the calculation for the formula could be slow because
1) Data is large
2) File may have other calculations running which could be recalculating

Don't know till I see how big the data is

 

[Abhijeet]

Countif is faster than sumproduct so it is helpful for large database.

Some one else give me coutifs solution at that time Narayan Sir told this is not work in few case of my data i check also.This Sumproduct look Overlap the dates.Your Formula is 100% work in my data i have to check with Narayan Sir & i will get back to u.

 

[Abhijeet]

Hi Chirayu

Can u pls tell me i want to copy paste where in formula grater than Zero That data to next work sheet.I am trying to do this with auto filter solution if u have any other way then pls tell me

 

[chirayu]

I've modified the macro

 

[Abhijeet]

Hi

This is i want only i want Data type 2 data pull in next sheet not entire row to copy paste in next sheet

 

[chirayu]

changed

 

[Abhijeet]

This is work but my question is if data is huge then this macro 1 by 1 cell go down & check & paste its take time if we use auto filter & copy paste the data its time save & single stroke paste the data please tell me on this

 

[Abhijeet]

I have changed sumproduct to counifs

Sorry deepk

I check with that formula Narayan sir told this formula not work in this
=COUNTIFS($A$3:$A$6,$F3,$B$3:$B$6,"<="&$H3,$C$3:$C$6,">="&$G3)

but i check ur solution its work

Sorry once again

 

[Abhijeet]

Hi

I tried to solve this for Faster result please tell me if this is correct or not
Please help me i want to use Last cell here i use (rng = Cells(Rows.Count, "A").End(xlUp).Row) but this is only column A last cell identify i want any of the column last cell then how to use code in VBA pls tell me

 

[chirayu]

Changed to autofilter. To speed up autofilter, I had to also value paste the sumproduct after it had been calculated, so if you want the formulas to come back, just copy the formula bit to the bottom of the macro. So that it calculates at the start & adds the formulas back in at the end.