LinkBack URL
About LinkBacks
Hi I want to count the qty of each code I require for the week, can I multiply the result of the COUNTIF formula by the days that they are required which is specified in the same row? (preferably in the same cell / formula).
Hi I want to count the qty of each code I require for the week, can I multiply the result of the COUNTIF formula by the days that they are required which is specified in the same row? (preferably in the same cell / formula).
Hello,
try
=SUMPRODUCT(($B$2:$B$12=K2)*($C$2:$I$12="yes"))
cheers,
Hi, do you mean counting the "Yes" per code?
Maybe in M2
=SUMPRODUCT(($B$2:$B$12=K2)*($C$2:$I$12="Yes"))
Hope it helps
Regards
-----------------
Sorry: I did not mean to overlap.
Last edited by canapone; 02-09-2011 at 04:26 PM.
Thanks to you both for your help, worked perfectly.
Thanks again
Ok so now is it possible to break that down into days so I could show how many of each I use on specific days. I have attached an updated sheet.
Thanks
You can use COUNTIFS, i.e. in M2 copied across and down
=COUNTIFS($B$2:$B$12,$K2,C$2:C$12,"Yes")
Audere est facere
That worked, thanks, now I am looking to group some of the days together ie codes required for Wed, Thurs and Fri will be delivered on a Tues and Sat, Sun, Mon and Tues will be delivered on a Fri.
I thought I could have altered the formula to get the Tues delivery easy enough but it seem not!!
Thanks again.
In that case, you're back with the Sumproduct.
in L9
=SUMPRODUCT(($B$2:$B$12=K9)*($E$2:$G$12="yes"))
copy down
in M9
=SUMPRODUCT(($B$2:$B$12=K9)*((C2:D12="yes")+($H$2:$I$12="yes")))
copy down
cheers,
Perhaps you could just sum the results from the other table you made using COUNTIFS()?
ie.
In cell L9
=SUM(O2:Q2)
In cell M9
=SUM(M2:N2,R2:S2)
Hi Teylyn, thanks for your help with this, your formula worked perfectly, I added $ into the forumla.
=SUMPRODUCT(($B$2:$B$12=K9)*(($C$2:$D$12="yes")+($H$2:$I$12="yes")))
Thanks again
Ok so moving it on a bit more.
I have insterted a new column (C) with a new range of "Alphbetic Codes" I would like to count up these codes much in the same way as the "numeric codes". The difference this time is when any of the "alphabetic codes" are requested with the "numeric code ending /1K" the qty is doubled. I have attached the updated sample.
In M16 & M19 the returned qty should be 4 & in N16, N16, Q16 & Q16 the qty should be 2.
Thanks again for any help
Last edited by Belfast_stu; 02-10-2011 at 03:33 PM. Reason: Updated sample sheet
Sorry just a bump for the nightshift!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks