Help with Sumproduct or similar formula to total items found by Sumproduct for invoices paid in a month.

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
153
Office Version
  1. 2013
Platform
  1. Windows
Hi All,

I have got the following formula in cells B2 to B7 and D2 to D7 in sheet called Invoices

Rich (BB code):
=SUMPRODUCT(--(TEXT($G$11:G40,"mmmm yyyy")=A2))

A2 currently has the value January 2021 but it can hold values of February 2021, March 2021….

It basically looks at range G11 to G40 and should it find January 2021, then it updates B2.

Therefore should it find 3 dates which fall in January 2021 in the range it updates B2 to 3.

What I would like to happen in E2:G2 is when it finds data that meets my criteria in G11 to G40, I would like it to sum the data in corresponding cells in K11:K40 and put the answer in E2, sum the data in corresponding cells in L11:L40 and put the answer in F2 and sum the data in corresponding cells in M11:M40 and put the answer in G2.

Therefore if the existing SUMPRODUCT find data matching my criteria in cells G11:G13, then E2 should be sum of cells K11:K13, F2 should be sum of the cells in L11:L13 and G2 should be the sum of the cells in M11:M13.

I need to redesign the sheet to allow the above summing to be reported for all other months, which I will do once I have got the above working.

I will be grateful for any assistance/suggestions offered.
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
153
Office Version
  1. 2013
Platform
  1. Windows
AhoyNC,

I have had a look at your recommendations and find that I made a boob (😖😬).

As you suggested the Vlookup's were indeed returning #N/A as they couldn’t find the Invoice number from workbook Invoice Issued.

I resolved this by increasing the lookup range in workbook Invoice Issued from J152 to 302 and also ensured each row had an invoice number. Therefore all formulas in sheet Invoices C11 to H11 now look like the following

=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$302,10,FALSE)

The above change has resulted in no more #N/A appearing in the sheet Invoices in columns C thru H as it can find the invoice numbers.

Once the above was done, the SUMPRODUCT formulas in sheet Invoice Payment Details worked a treat.

The reason why I had got it to work previously, was due to a small range (i.e. rows 11 to row 40 as indicated by my post #1, none of which had #N/A.

Once again, Thank you for all your assistance, pointers and patience.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Watch MrExcel Video

Forum statistics

Threads
1,132,913
Messages
5,655,919
Members
418,253
Latest member
TheJackal26

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