Sum if the adjacent cell is same as a cell in sperate tab AND another cell in separate tab is not empty

[Woodpusher147]

HI and thank you for any help given

IM sure its not to complex but I need help

IM looking for a formula to place in C5,6,7 etc (Tab 2) that will Sum cells in column C (Tab1) that contain the same code (L, M, XL) as column B (Tab2) This should only calculate IF J13 (Tab1) is not empty

This is TAB 2 where I need it to calculate the amount of L,M etc shirts that are black or white

Tab 1 is the order form
which is a little like this

There are also the 2 cells with black or white choice. An X would be placed in either depending which colour. All orders per order form are the same colour choice





IT really doesnt matter which cells these are, they can be A B C as these will change a little




Thanks again for any help

 

[Woodpusher147]

I have made this a little easier as I have put Tab 1 orders onto Tab 2 so all I need now is to SUM (in columnG) column D if column G is same as column C. BUT this should only SUM if Cell J13 in Tab 1 is not empty.
As all orders on each tab are the same colour, I couldnt bring that cell into tab 2

 

[Woodpusher147]

I have the formula to sum if C is the same as F sorry for the change in columns

=SUM(IF($C$8:$C$59=F10, $D$8:$D$59))

This works fine but I need this to ONLY calculate if cell J13 is not empty in a separate tab

Very grateful for any help

 

[Fluff]

What is the name of the sheet with J13?

 

[Woodpusher147]

What is the name of the sheet with J13?

Order 1

Thanks

 

[Fluff]

Ok, how about

=if(Order!J13<>"",SUMIFS($D$8:$D$59,$C$8:$C$59,F10),"")

 

[Woodpusher147]

Ok, how about

=if(Order!J13<>"",SUMIFS($D$8:$D$59,$C$8:$C$59,F10),"")

Thats great, thank you
However, I have, in trying to simplify, made things more complicated :/

There is more than one Order Sheet and each sheet would relate to one colour.
I brought all the results from the sheets into the Collation sheet thinking it would make things easier BUT if I calculate D8 to D59, this takes the results from all 5 sheets, some of which may have an "x" in black and some in white.

In that case, I think the formula would need to check the actual Order sheets and calculate from there, instead of bring the results into the final "Collation" sheet.

Could I make the IF statement check all sheets for an entry in J13? and sum from each sheet?

Sorry to go round in circles here but its quite hard to describe without showing.


Workbook has 6 sheet
ORder 1 to 5
&
Collation sheet

Each Order sheet has the orders and a choice of colour
Each Order sheet will relate to only one colour

Collation sheet needs to sum the total number of orders by size and colour

Your code works perfectly but ive got Order 2 / Prder 2 / ORder 3 etc all in D8 to D59 in the collation sheet. I didnt think about the fact that each sheet would be different colour

SO D8 - D15 is Order 1
D19 - D26 is Order 2
D30 - D37 is Order 3
D41 - D48 is Order 4
D52 - D59 is Order 5


Once again, thanks for any help

 

[Fluff]

You would be better of adding 2 extra columns to the collation sheet one for black & one for white & put an "X" in the relevant column for each row & use that in the sumifs.

 

[Woodpusher147]

You would be better of adding 2 extra columns to the collation sheet one for black & one for white & put an "X" in the relevant column for each row & use that in the sumifs.

Not sure what you mean.
Take the XL row with 185 results, some of these are black and some white. How would I tell how many of each

 

[Fluff]

How would I tell how many of each

By addiing the extra columns to siginify which are white & which are black.