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

#### Artorius

##### Board Regular
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Andrew Poulsom

##### MrExcel MVP
More like:

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

##### MrExcel MVP
=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
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

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

=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

Anyone else got any ideas on this? Thanks.

#### GlennUK

##### Well-known Member
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
Thanks GlennUK .... I've now fixed it by moving the brackets around .....

Replies
0
Views
142
Replies
3
Views
67
Replies
10
Views
232
Replies
19
Views
186
Replies
7
Views
102

### Forum statistics

1,136,909
Messages
5,678,514
Members
419,768
Latest member
eguechi09x ### 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.

### Which adblocker are you using?    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

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