Here are the data fields:-
H2:H10,000 = Vehicle Type (Used Or New vehicle code 0 or 1 only)
G2:G10,000 = Product Type (codes 0, 1, 2 or 4)
E2:E10,000 = Amount Financed
D2:D10,000 = Purchase Date in dd/mm/yyyy format
I need to provide a month by month, year by year Count and SUM by Used/New and Product Type. I have 3 SUMMARY worksheets for Count, 3 for SUM, each worksheet providing a Product Type breakdown.
On each SUMMARY worksheet, I have:-
Column A2:whatever ... YEAR
Column B2:whatever .... Month
So .... this is ...
A2 = 2000 B2 = January
A3 = 2000 B3 = February
etc etc down to .....
A13 = 2000 B13 = December
Then ....
A14 = 2001 B14 = January
etc etc.
Lets take the Product Type 0 summary sheet. In cell C3, I want to COUNT all contracts that have a Product Type of 0 AND a Vehicle Type of 0 where the Purchase Date matches the Year in Column A and the month in Column B.
In other words, I want an IF, IF, SUMPRODUCT formula. Here is the one that I produced .... but it is not working .... any thoughts?
=IF(H2:H10000=0),IF(G2:G10000=0),SUMPRODUCT((MONTH(D2:D10000)=MONTH(A3)*YEAR(D2:D10000)=YEAR(A2))
Or .... can you think of an easier way to do this?
The Amount Financed column by the way is for the next part that I want to do ... the SUM, not the count which is the formula above.
H2:H10,000 = Vehicle Type (Used Or New vehicle code 0 or 1 only)
G2:G10,000 = Product Type (codes 0, 1, 2 or 4)
E2:E10,000 = Amount Financed
D2:D10,000 = Purchase Date in dd/mm/yyyy format
I need to provide a month by month, year by year Count and SUM by Used/New and Product Type. I have 3 SUMMARY worksheets for Count, 3 for SUM, each worksheet providing a Product Type breakdown.
On each SUMMARY worksheet, I have:-
Column A2:whatever ... YEAR
Column B2:whatever .... Month
So .... this is ...
A2 = 2000 B2 = January
A3 = 2000 B3 = February
etc etc down to .....
A13 = 2000 B13 = December
Then ....
A14 = 2001 B14 = January
etc etc.
Lets take the Product Type 0 summary sheet. In cell C3, I want to COUNT all contracts that have a Product Type of 0 AND a Vehicle Type of 0 where the Purchase Date matches the Year in Column A and the month in Column B.
In other words, I want an IF, IF, SUMPRODUCT formula. Here is the one that I produced .... but it is not working .... any thoughts?
=IF(H2:H10000=0),IF(G2:G10000=0),SUMPRODUCT((MONTH(D2:D10000)=MONTH(A3)*YEAR(D2:D10000)=YEAR(A2))
Or .... can you think of an easier way to do this?
The Amount Financed column by the way is for the next part that I want to do ... the SUM, not the count which is the formula above.