SUMOFPROUDUCT SHOWING #N/A IN THE FORMULA

A

AYSHANA

Board Regular
Joined
Oct 16, 2021
Messages
90
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
good day to all

can anyone help me regarding my formula why it is showing as #N/A

im trying to count how many reasons i got per month.

BGRPS STATISTICS.xlsm
PQRWXYZAAABACAD
1202301-03-2023XM RECEIVED INSTEAD OF BGRPSjanfebmaraprmayjun
201-03-2023AVAILABLE BGRPS SAMPLECANCELLED/ NOT RECEIVED REASON#N/A#N/A#N/A#N/A#N/A#N/A
301-03-2023XM SAMPLE RECEIVEDDOUBLE ORDER#N/A#N/A#N/A#N/A#N/A#N/A
401-03-2023XM SAMPLE RECEIVEDDOUBLE ORDER / AVAILABLE BGRPS SAMPLE#N/A#N/A#N/A#N/A#N/A#N/A
505-03-2023AVAILABLE BGRPS SAMPLEDOUBLE ORDER / AVAILABLE XM SAMPLE#N/A#N/A#N/A#N/A#N/A#N/A
605-03-2023AVAILABLE UNITS FROM BGRPSDOUBLE ORDER / AVAILABLE UNITS FROM BGRPS#N/A#N/A#N/A#N/A#N/A#N/A
707-03-2023BGRPS RECEIVED INSTEAD OF XMDOUBLE ORDER / AVAILABLE UNITS FROM XM#N/A#N/A#N/A#N/A#N/A#N/A
808-03-2023DOUBLE ORDER UNITS ISSUED FROM BGRPSDOUBLE ORDER / UNITS ISSUED FROM AVAILABLE BGRPS#N/A#N/A#N/A#N/A#N/A#N/A
909-03-2023DOUBLE ORDERDOUBLE ORDER / UNITS ISSUED FROM AVAILABLE XM#N/A#N/A#N/A#N/A#N/A#N/A
1012-03-2023DOUBLE ORDER UNITS ISSUED FROM BGRPSDOUBLE ORDER / BGRPS RECEIVED INSTEAD OF XM#N/A#N/A#N/A#N/A#N/A#N/A
1113-03-2023DOUBLE ORDER UNITS ISSUED FROM BGRPSDOUBLE ORDER / XM RECEIVED INSTEAD OF BGRPS#N/A#N/A#N/A#N/A#N/A#N/A
1213-03-2023DOUBLE ORDER UNITS ISSUED FROM BGRPSDOUBLE ORDER / XM RECEIVED #N/A#N/A#N/A#N/A#N/A#N/A
1316-03-2023DOUBLE ORDER UNITS ISSUED FROM XMDOUBLE ORDER / BGRPS RECEIVED #N/A#N/A#N/A#N/A#N/A#N/A
1418-03-2023DOUBLE ORDER UNITS ISSUED FROM BGRPSDOUBLE ORDER / REQUESTED BGRP FOR PLT/FFP #N/A#N/A#N/A#N/A#N/A#N/A
1518-03-2023DOUBLE ORDERDEATH ON ARRIVAL#N/A#N/A#N/A#N/A#N/A#N/A
1618-03-2023DOUBLE ORDER UNITS ISSUED FROM BGRPSPT DISCHARGE #N/A#N/A#N/A#N/A#N/A#N/A
1720-03-2023DOUBLE ORDER UNITS ISSUED FROM XMPT EXPIRED#N/A#N/A#N/A#N/A#N/A#N/A
1821-03-2023DOUBLE ORDER UNITS ISSUED FROM BGRPSNO NEED FOR COLLECTION AS PER DR#N/A#N/A#N/A#N/A#N/A#N/A
1925-03-2023NOT RECEIVED PT IN OTPT REFUSED ADMISSION#N/A#N/A#N/A#N/A#N/A#N/A
2026-03-2023BGRPS SAMPLE RECEIVED NOT RECEIVED PT IN OT#N/A#N/A#N/A#N/A#N/A#N/A
2129-03-2023BGRPS SAMPLE RECEIVED PT FOR CHECK UP#N/A#N/A#N/A#N/A#N/A#N/A
2231-03-2023DOUBLE ORDER UNITS ISSUED FROM BGRPSPT STABLE#N/A#N/A#N/A#N/A#N/A#N/A
2301-04-2023DOUBLE ORDERPT SHIFTED#N/A#N/A#N/A#N/A#N/A#N/A
helper
Cell Formulas
RangeFormula
Q1:Q23Q1=VLOOKUP($K1,'not received'!$A2:E30000,5,0)
R1:R23R1=VLOOKUP($K1,'not received'!$A2:F30000,6,0)
Y2:AD23Y2=SUMPRODUCT(--($R$1:$R$2993=$X2),--(MONTH($Q$2:$Q$2993)=Y$1&$P$1),--($R$1:$R$2993))
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Possibly this is the issue MONTH($Q$2:$Q$2993)=Y$1&$P$1). You are trying to compare text and number with a number. The -- is then turning a true/false to 1 or 0. You will need to change jan to an number, if you want to include the year will need to add that to the second part of the formula
 
Upvote 0
I could not copy your post.
Try editing your syntax

I wasn't sure of the data; you may have to edit the ranges.

SUMPRODUCT(--($R$2:$R$2993=$X3),--(TEXT($Q$2:$Q$2993,"mmmyyyy")=Y$1&$P$1))

Try the formula on a range with just a few rows and review with Excel's formula evaluate.
 
Upvote 0
Another approach. The month headings are actual dates like Jun1 2023 custom formatted.

T202306a.xlsm
ABCDEFG
1DateInformationJun-23Jul-23
220-Jun-23ABCABC12
325-Jun-23XYZXYZ10
45-Jul-23ABC
57-Jul-23ABC
6e
Cell Formulas
RangeFormula
F2:G3F2=SUMPRODUCT(--($A$2:$A$5-DAY($A$2:$A$5)+1=F$1),--($B$2:$B$5=$E2))
 
Upvote 0
Dave Patton said:
Another approach. The month headings are actual dates like Jun1 2023 custom formatted.

T202306a.xlsm
ABCDEFG
1DateInformationJun-23Jul-23
220-Jun-23ABCABC12
325-Jun-23XYZXYZ10
45-Jul-23ABC
57-Jul-23ABC
6e
Cell Formulas
RangeFormula
F2:G3F2=SUMPRODUCT(--($A$2:$A$5-DAY($A$2:$A$5)+1=F$1),--($B$2:$B$5=$E2))
Click to expand...
THE PROBLEM IS THE COLUMN Q & R CONTAINS A FORMULA, IS THAT A REASON SHOWING #N/A
 
Upvote 0
Dave Patton said:
Another approach. The month headings are actual dates like Jun1 2023 custom formatted.

T202306a.xlsm
ABCDEFG
1DateInformationJun-23Jul-23
220-Jun-23ABCABC12
325-Jun-23XYZXYZ10
45-Jul-23ABC
57-Jul-23ABC
6e
Cell Formulas
RangeFormula
F2:G3F2=SUMPRODUCT(--($A$2:$A$5-DAY($A$2:$A$5)+1=F$1),--($B$2:$B$5=$E2))
Click to expand...
I HAVE ALSO TRIED YOURS BUT IT WAS STILL SHOWING #N/A
 
Upvote 0
@AYSHANA
Please review the Forum Rules, particularly #14 in relation to your thread title & posts 5 & 6.

There are a few things that are going wrong or could be going wrong
  1. Your SUMPRODUCT formula =SUMPRODUCT(--($R$1:$R$2993=$X2),--(MONTH($Q$2:$Q$2993)=Y$1&$P$1),--($R$1:$R$2993)) will fail because it does not have equal sized ranges. The R ranges got from 1:2993 but the Q range goes from 2:2993

  2. Your SUMPRODUCT formula =SUMPRODUCT(--($R$1:$R$2993=$X2),--(MONTH($Q$2:$Q$2993)=Y$1&$P$1),--($R$1:$R$2993)) will fail because of the red part. Column R is not numerical so trying to convert it to a number with -- will create an error.

  3. Your SUMPRODUCT formula =SUMPRODUCT(--($R$1:$R$2993=$X2),--(MONTH($Q$2:$Q$2993)=Y$1&$P$1),--($R$1:$R$2993)) will fail because of the coloured part. The red part will return a number in relation to the column Q dates, whereas the blue part will return text. Even if the red part returned the short text month (eg "Jan") the whole coloured part would still never count because the red part returns month only and the blue part returns month and year.

  4. Your SUMPRODUCT formula could fail if the VLOOKUP formulas in column Q or R ever fails to find the column K date in 'not received'. In that case the VLOOKUP would return a #N/A error which would flow to your SUMPRODUCT formulas causing them to also return #N/A
My suggestion then is to
  • Change the Q1 formula to =IFNA(VLOOKUP($K1,'not received'!$A2:E30000,5,0),"") & copy down

  • Change the R1 formula to =IFNA(VLOOKUP($K1,'not received'!$A2:F30000,6,0),"") & copy down

  • Change the Y2 formula to =SUMPRODUCT(--($R$1:$R$2993=$X2),--(TEXT($Q$1:$Q$2993,"mmmyyyy")=Y$1&$P$1)) and copy across and down.
 
Upvote 0
Solution
Peter_SSs said:
@AYSHANA
Please review the Forum Rules, particularly #14 in relation to your thread title & posts 5 & 6.

There are a few things that are going wrong or could be going wrong
  1. Your SUMPRODUCT formula =SUMPRODUCT(--($R$1:$R$2993=$X2),--(MONTH($Q$2:$Q$2993)=Y$1&$P$1),--($R$1:$R$2993)) will fail because it does not have equal sized ranges. The R ranges got from 1:2993 but the Q range goes from 2:2993

  2. Your SUMPRODUCT formula =SUMPRODUCT(--($R$1:$R$2993=$X2),--(MONTH($Q$2:$Q$2993)=Y$1&$P$1),--($R$1:$R$2993)) will fail because of the red part. Column R is not numerical so trying to convert it to a number with -- will create an error.

  3. Your SUMPRODUCT formula =SUMPRODUCT(--($R$1:$R$2993=$X2),--(MONTH($Q$2:$Q$2993)=Y$1&$P$1),--($R$1:$R$2993)) will fail because of the coloured part. The red part will return a number in relation to the column Q dates, whereas the blue part will return text. Even if the red part returned the short text month (eg "Jan") the whole coloured part would still never count because the red part returns month only and the blue part returns month and year.

  4. Your SUMPRODUCT formula could fail if the VLOOKUP formulas in column Q or R ever fails to find the column K date in 'not received'. In that case the VLOOKUP would return a #N/A error which would flow to your SUMPRODUCT formulas causing them to also return #N/A
My suggestion then is to
  • Change the Q1 formula to =IFNA(VLOOKUP($K1,'not received'!$A2:E30000,5,0),"") & copy down

  • Change the R1 formula to =IFNA(VLOOKUP($K1,'not received'!$A2:F30000,6,0),"") & copy down

  • Change the Y2 formula to =SUMPRODUCT(--($R$1:$R$2993=$X2),--(TEXT($Q$1:$Q$2993,"mmmyyyy")=Y$1&$P$1)) and copy across and down.
Click to expand...
thank you its working 🥹
 
Upvote 0
You're welcome. Thanks for the confirmation. :)

.. also thanks for the lower case post. (y)
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Upgrade assist to a formula
2
Replies
10
Views
865
robbarba
R
S
  • Question Question
ETA Calculation based on orders
Replies
0
Views
2K
sanket_sk
S
C
  • Question Question
Ton of script in case anyone wants to review, maybe something useful to someone
Replies
0
Views
3K
cizzett
C
J
  • Question Question
Extract Names from long String with specific criteria?
2
Replies
13
Views
2K
jeffcoleky
J
R
  • Question Question
Macros lagging and now crashing after working perfectly - can't find problem.
Replies
6
Views
2K
rdetreville
R

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

Share this page

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