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

[Kayslover]

Hi All,

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

=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]

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.