LinkBack URL
About LinkBacks
Hi
Can anyone see why this sum product isn’t working please. In cell P13 I would expect £700 to be returned as H13 contains either 1104 or 1008
I have uploaded a file – it has worked previously but something must be corrupting it.
Paul
Hi
Can anyone see why this sum product isn’t working please. In cell P13 I would expect £700 to be returned as H13 contains either 1104 or 1008
I have uploaded a file – it has worked previously but something must be corrupting it.
Paul
In P13 Cell
=SUMPRODUCT((LEFT($H$13:$H$14,4)="1104")+(LEFT($H$13:$H$14,4)="1108")*(LEFT($G$13:$G$14,3)=$O13),($K$13:$K$14))
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Try this..
=SUMPRODUCT((LEFT($H$13:$H$14,4)="1104")*($J$13:$J$14=0)*(LEFT($G$13:$G$14,3)=TEXT($O13,"000")),$K$13:$K$14)+SUMPRODUCT((LEFT($H$13:$H$14,4)="1108")*($J$13:$J$14=0)*(LEFT($G$13:$G$14,3)=TEXT($O13,"000")),$K$13:$K$14)
Life's a spreadsheet, Excel!
Say thanks, Click *
@Daddylonglegs - you star! I have looked at the problem for ages and I wouldnt have spotted it!! It was "" instead of 0 which I have now changed
The formula is working as it should - one of your criteria is $J$13:$J$14="".......but neither of those cells are ="" or blank - they both contain zero, which isn't the same thing - you either need to remove that condition, or change it to $J$13:$J$14=0.......or perhaps change the data.......
Audere est facere
Hi,
Thanks for the replies - both work. I will use ACE_XL's as this includes both columns.
Does anyone know why the orgianal formula wouldn't work - I can not see why?
Paul
=SUMPRODUCT((LEFT($H$13:$H$14,4)="1104")*($J$13:$J$14="")*(LEFT($G$13:$G$14,3)=$O13)*($K$13:$K$14))+SUMPRODUCT((LEFT($H$13:$H$14,4)="1108")*($J$13:$J$14="")*(LEFT($G$13:$G$14,3)=$O13)*($K$13:$K$14))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks