New Microsoft Excel Worksheet.xlsx
Hello all.
The more time I spend looking at formulae the more confused I get.
I am having a hard time telling when I need to use SumProduct, Countif(s), sumif(s) etc.
I was already assisted here with the following formula:
=SUMPRODUCT(--(rngDeskIss>=G3)*(rngDeskIss<=I3)*(rngDeskIss=rngSch1Sub))
However, it does not seem like I can just ‘plug and play’ with this the way I intended.
This correctly matches a named_range of cells within a given date range (G/I). If the date is between G&I, and the two items match, sum.
This works perfectly for my one question, but I cannot seem to apply this to a >, <, for a different kind/set of data.
For my new example, I am looking to pull data between a set range (J3/L3), and if they data in column A is between J3:L3, count if column B is above B1.
Or worded differently:
Count the number of times the values in column B less than cell B1, which is pulled from a range of dates defined by J3/L3.
How many items between 4/1 and 4/8 are less than '730'?
4/4/2013 - 732
4/5/2013 - 730
4/8/2013 - 725
I have tried to modify the following examples I've found, without luck:
'=SUMPRODUCT(--(DataPull!$O$2:$O$10000=DataPull!$P$2:$P$10000))
'=SUMPRODUCT((COUNTIF(B2:B1000,A2:A1000)=0)*(A2:A1000<>""))
'=SUMPRODUCT(--(rngDeskIss>=G3)*(rngDeskIss<=I3)*(rngDeskIss=rngDeskSub))
'=COUNTIFS($A$2:$A$9,">="&$F$1,$A$2:$A$9,"<="&$F$2,B$2:B$9,"="&$F$4)
'=counfifs(($A$2:$A$100>=V51)*($A66:X$100<=X5)*($B$2:$B$100))
Bookmarks