IF ... IF .... SUMPRODUCT by Month & Year Beast of a For

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
More like:

=SUMPRODUCT(--(YEAR(D2:D10000)=A2),--(MONTH(D2:D10000)=MONTH("1/"&B2)+0),--(H2:H10000=0),--(G2:G10000=0))
 
Upvote 0
=SUMPRODUCT(--($D$2:$D$10000-DAY($D$2:$D$10000)+1=("1-"&B2&"-"&A2)+0),--($H$2:$H$10000=0),--($G$2:$G$10000=0))

should return the desired count.

=SUMPRODUCT(--($D$2:$D$10000-DAY($D$2:$D$10000)+1=("1-"&B2&"-"&A2)+0),--($H$2:$H$10000=0),--($G$2:$G$10000=0),$E$2:$E$10000)

should return the desired total.
 
Upvote 0
Thanks guys ....

Andrew ..... formula is giving me an Value error and when I step through it ... it seems to be stalling on this part ..... MONTH("1/"&B2)+0) part of the formula is creating a #VALUE! error. This is where I have the month name entered in the B2 cell only.

I'll take another look tomorrow and attempt Aladin's formula too ...
 
Upvote 0
If B2 contains the text string January:

=MONTH("1/"&B2)+0)

will return 1. But if B2 contains a date formatted as MMMM to return the name of the month you need only:

SUMPRODUCT(--(YEAR(D2:D10000)=A2),--(MONTH(D2:D10000)=B2),--(H2:H10000=0),--(G2:G10000=0))
 
Upvote 0
Aladin ... the

=SUMPRODUCT(--($D$2:$D$10000-DAY($D$2:$D$10000)+1=("1-"&B2&"-"&A2)+0),--($H$2:$H$10000=0),--($G$2:$G$10000=0),$E$2:$E$10000)

is not returning a value. I had to insert a ( on the end part of the formula but excel then changes the formula to this .......

=SUMPRODUCT(--(PCP!$B$2:$B$36000-DAY(PCP!$B$2:$B$36000)+1=("1-"&C4&"-"&B4)+0),--(PCP!$H$2:$H$36000=0),--(PCP!$G$2:$G$36000=0))*(PCP!$E$1:$E$36000)

This is the actual formula that I'm using.

Any thoughts? I need to get the SUM. The Count part seems to be working ok.
 
Upvote 0
Well, you've moved a ")" so that *(PCP!$E$1:$E$36000) is outside the SUMPRODUCT. So that isn't going to do any good.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top