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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
More like:

=SUMPRODUCT(--(YEAR(D2:D10000)=A2),--(MONTH(D2:D10000)=MONTH("1/"&B2)+0),--(H2:H10000=0),--(G2:G10000=0))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
=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.
 

Artorius

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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))
 

Artorius

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

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176

ADVERTISEMENT

Anyone else got any ideas on this? Thanks.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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.
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
Thanks GlennUK .... I've now fixed it by moving the brackets around .....
 

Forum statistics

Threads
1,141,608
Messages
5,707,368
Members
421,505
Latest member
whittirs

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
Top