Sum/if/count etc from 1 tab to another

[Ira Hopkins]

Hi

I have a spreadsheet with 2 tabs. 1 is called histories and the other is order- ( it wont let me do add in to attach file as work fire walls to do as attachment)
I need the type on histories to fill in ok if more than one entry on orders but if only one entry to say exactly what is on type column in order tab. Also if the code appears more than once on order tab I need to add together for histories tab on the total column and again if not put what it is from order tab. Thanks so much
Top is the order tab and bottom is history tab-

Code	date	Type	Total
F2091	01/05/2024​	Normal	£10​
F2092	02/05/2024​	Normal	£10​
F2093	03/05/2024​	Normal	£10​
F2094	04/05/2024​	Normal	£10​
F2095	05/05/2024​	Normal	£10​
F2096	06/05/2024​	Normal	£10​
F2097	07/05/2024​	Normal	£10​
F2098	08/05/2024​	Normal	£10​
F2099	09/05/2024​	Normal	£10​
F2100	10/05/2024​	Normal	£10​
F2091	01/05/2024​	Urgent	£12​
F2092	02/05/2024​	Urgent	£12​
F2093	03/05/2024​	Urgent	£12​

Code	date	Type	Total
F2091
F2092
F2093
F2094
F2095
F2096
F2097
F2098
F2099
F2100
F2091
F2092
F2093

 

[awoohaw]

try this, please:
Note: there can be more succinct formulas if you use a later version of excel. Please update your profile so the forum can give you the best solution.
also, you have repeated codes on your history tab.

Book1
	A	B	C	D
1	Code	date	Type	Total
2	F2091		OK	£ 22.00
3	F2092		OK	£ 22.00
4	F2093		OK	£ 22.00
5	F2094		Normal	£ 10.00
6	F2095		Normal	£ 10.00
7	F2096		Normal	£ 10.00
8	F2097		Normal	£ 10.00
9	F2098		Normal	£ 10.00
10	F2099		Normal	£ 10.00
11	F2100		Normal	£ 10.00
12	F2091		OK	£ 22.00
13	F2092		OK	£ 22.00
14	F2093		OK	£ 22.00
-history

Cell Formulas
Range	Formula
C2:C14	C2	=IF(COUNTIFS(order!$A$3:$A$15,A2)>1,"OK",IFERROR(INDEX(order!$C$3:$C$15, MATCH(A2,order!$A$3:$A$15,0)),""))
D2:D14	D2	=SUMIF(order!$A$3:$A$15,A2,order!$D$3:$D$15)

 

[Ira Hopkins]

Hi the first one is brilliant thanks so much for your help. The 2nd formula I only want it to add together the same codes- its adding all the codes. Thanks again

 

[awoohaw]

For 2nd, what are examples of expected results? What cell(s) are correct or incorrect?

and again... what version of excel are you using?

 

[Ira Hopkins]

Microsoft® Excel® for Microsoft 365 MSO (Version 2404 Build 16.0.17531.20140) 32-bit

Example is for code F2091 (order A2 &A12) the sum should read £22 on cell d2 on total tab - thanks again

 

[awoohaw]

Microsoft® Excel® for Microsoft 365 MSO (Version 2404 Build 16.0.17531.20140) 32-bit

Example is for code F2091 (order A2 &A12) the sum should read £22 on cell d2 on total tab - thanks again

i'm confused. Look at what I posted, cell D2 has £22. I do have a typo in one of the formulas and will post a more accurate 365 soluction

 

[Ira Hopkins]

Hi - i need it to go into the totals column on the histories tab- sorry I am not explaining myself very well but not very good at excel. When i put it on my spreadsheet it did a sum of all the totals not like yours- I must be doing something wrong

 

[awoohaw]

Hi - i need it to go into the totals column on the histories tab- sorry I am not explaining myself very well but not very good at excel. When i put it on my spreadsheet it did a sum of all the totals not like yours- I must be doing something wrong

i did have a typo so, i didnot include first cell of one column. I'll post a 365 solution in a moment.

 

[awoohaw]

Please check the starting row and column references in the formulas. Since you are unable to use the xl2bb i did not know these.
Here is a possibility with the xlookup function in column C. formula in column D is the same (but with updated references). This is assuming your data starts on row 2 on each worksheet:

Book1
	A	B	C	D
1	Code	date	Type	Total
2	F2091		OK	£ 22.00
3	F2092		OK	£ 22.00
4	F2093		OK	£ 10.00
5	F2094		Normal	£ 10.00
6	F2095		Normal	£ 10.00
7	F2096		Normal	£ 10.00
8	F2097		Normal	£ 10.00
9	F2098		Normal	£ 10.00
10	F2099		Normal	£ 10.00
11	F2100		Normal	£ 12.00
12	F2091		Normal	£ 22.00
13	F2092		Normal	£ 22.00
14	F2093		Normal	£ 10.00
-history

Cell Formulas
Range	Formula
C2:C14	C2	=IF(COUNTIFS(order!$A2:$A$14,A2)>1,"OK", XLOOKUP(A2,order!$A$2:$A$14,order!$C$2:$C$14,"not found",0))
D2	D2	=SUMIF(order!$A$2:$A$14,A2,order!$D$2:$D$14)
D3:D14	D3	=SUMIF(order!$A$1:$A$14,A3,order!$D$2:$D$14)

 

[Ira Hopkins]

Its worked thank you so much for all your help greatly appreciatted