LinkBack URL
About LinkBacks
Hello:
Please refer to attached file.
Sheet1 has data.
I need formula in Sheet2 to give Invoice total for each state and for each month.
Let me know if you have any questions.
Thanks.
Riz
Hello:
Please refer to attached file.
Sheet1 has data.
I need formula in Sheet2 to give Invoice total for each state and for each month.
Let me know if you have any questions.
Thanks.
Riz
TRY:
=SUMPRODUCT(--(Sheet1!$R$2:$R$8=Sheet2!$A2),--(Sheet1!$A$2:$A$8=Sheet2!B$1),(Sheet1!$H$2:$H$8)) sheet2 B2
copy across and down
Here's the SUMIFS version...Originally Posted by JohnTopley
=SUMIFS(Sheet1!$H$2:$H$8,Sheet1!$R$2:$R$8,Sheet2!$A2,Sheet1!$A$2:$A$8,Sheet2!B$1)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi John:
I am using on my workbook with the following formula but it giving #Name? error.
Can you please check to see if there is any error:
=SUMPRODUCT(--(Invoice detail!$R$2:$R$22627=Riz!$A2),--(Invoice detail!$A$2:$A$22627=Riz!B$1),(Invoice detail!$H$2:$H$22627))
Thanks
Riz
With a pivot table.
See the attahced file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Since your sheet name contains a space the name should be enclosed in single inverted commas. It's always good practice anyway even if there are no spaces since if the sheet name is ever changed to one with a space the formula will continue to work.
i.e.
Formula:
Please Login or Register to view this content.
However Tony's suggestion of using SUMIFS is IMO preferable. Sumproduct is somewhat old hat these days and was usually just a work around for Excel 2003 & prior.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Like this...
=SUMIFS('Invoice detail'!$H$2:$H$22627,'Invoice detail'!$R$2:$R$22627,Riz!$A2,'Invoice detail'!$A$2:$A$22627,Riz!B$1)
Hi Guys:
Now it worked after adding ' ' aroung Invoice detail.
Would it be possible to have H a variable which i can have at lets say in cell N1.
So basically i have lots of column and i will have dropdown list at cell N1 to select which column to evaluate.
Please let me know if you have any questions.
Thanks.
Riz
How about this...
Have a drop down list in cell N1 that has the column headers from the Invoice detail sheet range B1:L1 as the selections.
Then, the formula would be...
=SUMIFS(INDEX('Invoice detail'!$B$2:$L$22627,0,MATCH($N$1,'Invoice detail'!$B$1:$L$1,0)),'Invoice detail'!$R$2:$R$22627,Riz!$A2,'Invoice detail'!$A$2:$A$22627,Riz!B$1)
if you use the solution of a pivot table (#5) you don't have to change the month, since all months are available.
See my example in #5.
Hi Guys:
All of you had great solutions and i will use them.
Thanks a lot for great help.
Riz
You're welcome. We appreciate the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks