couny data according to month or full year

[shj92922]

attached my dummy Excel file that had same variables to my real one

i am working on dashboard i want to know how many report is done within selected month(C4) or full year
so what i need is when i select from the list what i need it automatically give me the data (D4:D7)

[shj92922]

please check this file here

[jason.b75]

I don't understand what you're trying to do. You already have formulas that appear to be giving the correct results.

If they are not correct then please tell us what the correct results should be.

[shj92922]

i want E4:E7 table represent full year 2018 records
but D4:D7 table represent monthly records
so what i want is at D4:D7 table will represent year + month depending on C4 selection
now on current sheet if you select 2018 (C4) nothing will give you the formula only for month (forget about E4:E7 table )
hope you understand

[jason.b75]

So you want to be able to choose Jan 2018 or Jan 2019 (for example)?

[shj92922]

let make it simple if i choose 2018 at Cell (C4) it will give same result at E4:E7 table
Currently it will give 0 Values for 2018
Jan it will give me Jan records

[jason.b75]

This assumes that sheet 2 will only ever contain data for a single year.

=COUNTIFS(Sheet2!$A:$A,IF($C$4=2018,"<>",$C$4),Sheet2!$I:$I,"<0")

If you have data for the same month in 2 different years in your sheet then the dropdown will need to be changed to contain real dates and the counts compared to the dates in columns C or D rather than the tet strings in column A.

[shj92922]

ok man thanks you solve it Reputation added too
buti didnt understand this "<>" is it mean equal can you give brief answer
thanks again

[jason.b75]

No, that is not equal.

The idea being that if a month is selected then the formula looks for that month in column A, if 2018 is selected then it looks for anything that is not 2018, which would be everything.

This is one of the reasons why I said that it will not work if you have data for more than 1 year in the sheet.

[shj92922]

ok man thanks a lot now i understand

for my sheet i make record for each year Separately so i guess your formula will work just i need to change 2018