Countif formula with multi variables

[Largey980]

Good Afternoon,

After some help with a countif?

I need to count number of entries on sheet 2, with two variables; only count if (C+D) < 1 year ago.

My raw Data is in two formats so needs substitute for search.
Just not sure how to add in extra variables:

Sheet1
A B C
Month Apr
Ref Count Amount
8230000 7 ?

Formula I using so far
=IF(COUNTIFS(Sheet2!B:D,SUBSTITUTE(LEFT(A4,3)&"/"&RIGHT(A4,4),"",""))=0,"",(COUNTIFS(Sheet2!B:D, SUBSTITUTE(LEFT(A4,3)&"/"&RIGHT(A4,4),"",""))))

Which returns 7

Sheet2
A B C D
Amount Ref Year Month
10 823/0000 2017 Jan X
10 823/0000 2017 Feb X
10 823/0000 2017 Mar X
10 823/0000 2017 Jun
10 823/0000 2017 Jul
10 823/0000 2017 Aug
10 823/0000 2016 Jan X

So I need to only count if sheet2 date (C/D) is < 1 year from this month.

Looking for a return of 4 .

Once I have the 4 as correct entries, I need to also count the amounts of these 4 in Sheet2 A

Any help would be appreciated.
I can add additional column to sheet1 if required but sheet2 is raw extract data and is stuck.

Plus I really want to see if it can be done on one countif.

Thanks

 

[oldbrewer]

maybe start by using a helper column to convert eg the first row to 1/1/17

 

[Largey980]

maybe start by using a helper column to convert eg the first row to 1/1/17

yes, i can edit sheet1 so Apr > 01/04/2017 is fine

 

[Martvg]

Hi Largey,

I'm trying to understand your substitute function, but right now you seem to concatenate a string and then substitute nothing within this string with nothing.
Can you please explain what you achieve by doing this?

And can you inform me of the contents of sheet1 A4?

 

[Largey980]

Hi Martvg,

Concatenate would have been better to use, just used substitute as it was first thing to pop into my head, recently used for removing spaces, although not needed here.

so revised
=IF(COUNTIFS(Sheet2!B:D,CONCATENATE(LEFT(A4,3)&"/"&RIGHT(A4,4)))=0,"",(COUNTIFS(Sheet2!B:D,CONCATENATE(LEFT(A4,3)&"/"&RIGHT(A4,4)))))

Shee1A4 would hold the next ref 8230001 - which also needs same check on sheet2 (just snipped it down )

 

[Martvg]

the following formula returns 4:

=IF(COUNT(IF((DATEVALUE(1&"/"&Sheet2!D2:D8&"/"&Sheet2!C2:C8)<=TODAY())*(Sheet2!B2:B8=LEFT(A4,3)&"/"&RIGHT(A4,4)),Sheet2!A2:A8))=0,"",COUNT(IF((DATEVALUE(1&"/"&Sheet2!D2:D8&"/"&Sheet2!C2:C8)<=TODAY())*(Sheet2!B2:B8=LEFT(A4,3)&"/"&RIGHT(A4,4)),Sheet2!A2:A8)))

To get the sum of a simply change COUNT with SUM. When entering this formula hit CTRL+SHIFT+ENTER to create an array formula.

I wasn't sure what you meant with < 1 year ago, and assumed you meant more than a year ago, but this didn't correspond with your data. Therefore the formula above simply checks if the date is in the past or not.

 

[Martvg]

and if you want it to be more than a year ago:

=IF(COUNT(IF((DATEVALUE(1&"/"&Sheet2!D2:D8&"/"&Sheet2!C2:C8)<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*(Sheet2!B2:B8=LEFT(A4,3)&"/"&RIGHT(A4,4)),Sheet2!A2:A8))=0,"",COUNT(IF((DATEVALUE(1&"/"&Sheet2!D2:D8&"/"&Sheet2!C2:C8)<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*(Sheet2!B2:B8=LEFT(A4,3)&"/"&RIGHT(A4,4)),Sheet2!A2:A8)))

Again, confirm the formula with CTRL+SHIFT+ENTER to create an array formula.

 

[Largey980]

Martvg - this worked perfectly.

I will have a work to deconstruct it, for learning purposes, but I am very pleased!

Many many thanks!

 

[Martvg]

Glad the formula helped you out, don't be put off by the number of functions in there .

 

[Largey980]

I called it too early, I cant get it to work.

(To keep things simple I created a smaller version of sheet, with same data ranges, i cannot get it working on actual sheet)

The sheet2 sample document, in actual document is sheet3.
Columns actually are: Amount = AB / Ref = AC / Year = AE / Month = AF
this list contains 2750 rows of refs

My actual sheet2 which holds the formula (listed above) has 40 refs to lookup
so this formula need to be duplicated 40 + 40 sum of amounts.

Sadly the data I need to capture is within 1 year of this month - eg this month April 2016 - April 2017

Any further help with this would be massively appreciated and a big time saver if manage to achieve it??