Hi, I am getting an error that says a value used in the formula is of the wrong data type. I have attached my workbook. The formula is at Sheet 2 cell B2.
My formula is =SUMIFS(Sheet1!$C$2:$N$34,Sheet1!$B$2:$B$34,Sheet2!$A2,Sheet1!$O$2:$O$34,Sheet2!$N2,Sheet1!$P$2:$P$34,Sheet2!$O2,Sheet1!C1:N1,Sheet2!B1)
Thanks for any of your help!
P.S: The information in this excel sheet is random and non-fiction.
Hi
Sheet2 cell B2 =SUMPRODUCT((Sheet1!$B$2:$B$34=$A2)*(Sheet1!$C$1:$N$1=B$1)*(Sheet1!$O$2:$O$34=$N2)*(Sheet1!$P$2:$P$34=$O2),Sheet1!$C$2:$N$34) Then copy down and cross
regard
micope21
To help you by my post? it would be nice to click onto say "Thank you".
If you are happy with a solution to your problem?
Click Thread Tools above your first post,
select "Mark your thread as Solved".
Hi
Sheet2 cell B2 =SUMPRODUCT((Sheet1!$B$2:$B$34=$A2)*(Sheet1!$C$1:$N$1=B$1)*(Sheet1!$O$2:$O$34=$N2)*(Sheet1!$P$2:$P$34=$O2),Sheet1!$C$2:$N$34) Then copy down and cross
regard
micope21
Hi, the formula provided works but I would like to know the reason why my formula doesn't work so I wouldn't make the same mistake the next time. Any idea? Cos I'm really clueless.
=SUMIFS(Sheet1!$C$2:$N$34,Sheet1!$B$2:$B$34,Sheet2!$A2,Sheet1!$O$2:$O$34,Sheet2!$N2,Sheet1!$P$2:$P$34,Sheet2!$O2,Sheet1!C1:N1,Sheet2!B1)
The problem is that all the ranges have to be the same size. Your sum range is 2 dimensional while the criteria ranges are 1 dimensional.
Also, the last criteria range is horizontal while the other criteria ranges are vertical.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks